If you want to know what is JOIN in SQL then, I think you have worked with data in a single table and as we know that fetching data from a single table is easy but if you want to fetch data from multiple tables then JOIN Clause is used.
In this era, databases generally have data in more than one table. If we want to be able to work with that data, we will have to combine multiple tables within a query with the help of join. We are assuming that you know the fundamentals of working in SQL including filtering, sorting, and aggregation.
In this SQL Join full tutorial, we will see types of joins and how to select data from multiple tables using the join clause in SQL. So let's start without wasting any time:-
What is a join?
Join is a standard concept in SQL which is used to combine rows from two or more tables based on a related column between those tables. Join is an advanced concept in SQL that you must know, as it helps to store data from multiple tables in different tables that we want to use further. Let's see an example:-
Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_name
FROM student
JOIN studentcourse ON studentcourse.std_roll=student.std_roll;
Why use join?
Suppose you have a huge amount of data in your database with many tables and you want to extract some columns from different tables then you can use the join clause in SQL because you can retrieve data from two or more tables in a single query. So join is one of the most used commands in SQL as in Data Analytics and in Data Science.
Here is my Student Table:
Here is my StudentCourse Table:
What are the different types of joins in SQL?
There are many different types of join in SQL but in this article, we will discuss only 7 types of Join clauses in SQL.
1. INNER JOIN / JOIN
The INNER JOIN or JOIN clause selects values that have matching values in both tables.
Inner Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_name
FROM student
INNER JOIN studentcourse ON studentcourse.std_roll=student.std_roll; Inner Join Example:-
Discover More
2. LEFT JOIN / LEFT OUTER JOIN
The LEFT JOIN clause returns all values from the left table (table1), and the matching records from the right table (table2). The result is 0 values from the right side if there is no match.
Left Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_na
FROM student
Left JOIN studentcourse ON studentcourse.std_roll=student.std_roll; Left Join Example:-
3. RIGHT JOIN / RIGHT OUTER JOIN
The RIGHT JOIN clause returns all values from the right table (table2), and the matching records from the left table (table1). The result is 0 values from the left side if there is no match.
Right Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_na
FROM student
Right JOIN studentcourse ON studentcourse.std_roll=student.std_roll; Right Join Example:-
4. FULL JOIN / FULL OUTER JOIN
The FULL OUTER JOIN clause returns all values when there is a match in the left (table1) or right (table2) table records.
Full Join is not supported in MySQL so I have used UNION which is the same as Full Join. If you are using MySQL then you can use UNION Method to perform Full Join in SQL.
Full Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_name FROM student
LEFT JOIN studentcourse ON student.std_roll=studentcourse.std_roll
UNION
SELECT student.std_roll, student.std_name, studentcourse.course_name FROM student
Right JOIN studentcourse ON student.std_roll=studentcourse.std_roll; Full Join Example:-
5. CROSS JOIN / Cartesian Product
In SQL, CROSS JOIN is used to join every row from the first table with every row from the second table. Cross join is also known as a Cartesian join because it returns the Cartesian product of the row group of the joined table.
Cross Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_name
FROM student
Cross JOIN studentcourse ON studentcourse.std_roll=student.std_roll; Cross Join Example:-
6. NATURAL JOIN
In natural join, the associated tables have one or more pairs of identically named columns and the columns must be the same data type. and please don't use the ON clause in the natural join. A natural join is a SQL join operation that creates a join based on common columns in a table. To perform a natural join, there must be a common attribute (column) between the two tables. Natural links result from multiple relationships.
Natural Join Query:
SELECT student.std_roll, student.std_name, studentcourse.course_name
FROM student
Natural JOIN studentcourse; Natural Join Example:-
7. SELF JOIN
SELF JOIN in SQL, as the name suggests, is used to join a table with itself. This means that each row in the table is related to itself and every other row in that table. However, referencing the same table multiple times in a query will result in an error. To avoid this, the alias SQL SELF JOIN is used.
Self Join Query:
SELECT a.std_roll , b.std_name
FROM Student a, Student b
WHERE a.std_roll > b.std_roll; Self Join Example:-
Conclusion:
In this article, we have covered the complete SQL Join clause for learners. If you want to dive into data science or data analytics then the JOIN clause will help you to play with a Database to store data. Soon we will cover SQL complete tutorial so please be connected with us.
You should also check out, Django Developer Roadmap, Python Developer Roadmap, C++ Complete Roadmap, Machine Learning Complete Roadmap, Data Scientist Learning Roadmap, R Developer Roadmap, DevOps Learning Roadmap, and Laravel Developer Roadmap.
Thank you for reading this blog. I wish you the best in your journey to learning and mastering SQL.
Follow me to receive more useful content:
Instagram | Twitter | Linkedin | Youtube
Thank you