Joins in SQL
In SQL, a JOIN clause is used to combine rows from two or more tables, based on a related column between them. It allows you to merge data from multiple different tables into a single result. This can be incredibly beneficial when you have data spread across different tables, but want to include it in a single query result for analysis or operations.
In this tutorial, we will cover different types of JOINs and provide examples to help you understand how each type of JOIN works.
Types of Joins in SQL
There are four basic types of JOINs that SQL supports:
- INNER JOIN
- LEFT JOIN (or LEFT OUTER JOIN)
- RIGHT JOIN (or RIGHT OUTER JOIN)
- FULL JOIN (or FULL OUTER JOIN)
INNER JOIN
An INNER JOIN returns records that have matching values in both tables.
Consider the following two tables:
Table-1: Employees
| ID | Name |
|---|---|
| 1 | Tom |
| 2 | Ana |
| 3 | Tim |
Table-2: Department
| ID | Dept |
|---|---|
| 1 | HR |
| 2 | IT |
| 4 | Marketing |
If we perform an INNER JOIN on these two tables based on the ID column, the result would be:
| ID | Name | Dept |
|---|---|---|
| 1 | Tom | HR |
| 2 | Ana | IT |
As you can see, the INNER JOIN only includes the records where the ID value is in both the Employees and Department tables.
LEFT (OUTER) JOIN
A LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL on the right side, if there is no match.
Using the same tables as above, a LEFT JOIN based on the ID column would return:
| ID | Name | Dept |
|---|---|---|
| 1 | Tom | HR |
| 2 | Ana | IT |
| 3 | Tim | NULL |
As you can see, it includes all records from the Employees table, but only includes records from the Department table where there is a matching ID.
RIGHT (OUTER) JOIN
A RIGHT JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL on the left side, if there is no match.
Again, using the same tables and performing a RIGHT JOIN based on the ID column, the result would be:
| ID | Name | Dept |
|---|---|---|
| 1 | Tom | HR |
| 2 | Ana | IT |
| 4 | NULL | Marketing |
It includes all records from the Department table, but only includes records from the Employees table where there is a matching ID.
FULL (OUTER) JOIN
A FULL JOIN returns all records when there is a match in either left (table1) or right (table2) table records.
If we perform a FULL JOIN on the same tables based on the ID column, the result would be:
| ID | Name | Dept |
|---|---|---|
| 1 | Tom | HR |
| 2 | Ana | IT |
| 3 | Tim | NULL |
| 4 | NULL | Marketing |
As you can see, the FULL JOIN includes all records from both tables, and fills in NULLs for missing matches on either side.
Conclusion
Joins are a powerful tool in SQL which allows you to consolidate data from multiple tables into a single result based on common column(s). Understanding how to use each type of JOIN can greatly enhance your ability to manipulate and analyze data in SQL.
Remember, practice is key when it comes to mastering SQL. Try creating your own tables and experimenting with different JOIN types and conditions to see the results. Happy querying!