When working with SQL and relational databases, joins are essential tools that allow you to combine data from multiple tables based on related columns. Among the various types of joins, Left Join, Right Join, and Full Outer Join are commonly used for different scenarios. Understanding the differences between these joins is crucial for efficient data querying and analysis. This article explains the distinctions between Left Join, Right Join, and Full Outer Join, helping you choose the right join for your needs.
Types of Outer Join
Outer join is again classified into 3 types: Left Outer Join, Right Outer Join, and Full Outer Join. These are explained as follows.
What is Left Outer Join?
Left Outer Join returns all the rows from the table on the left and the columns of the table on the right are null padded. Left Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the left table. Syntax:
SELECT [column1, column2, ....]
FROM table1
LEFT OUTER JOIN table2 ON
table1.matching_column = table2.matching_column
WHERE [condition];
Or
SELECT [column1, column2, ....]
FROM table1
LEFT OUTER JOIN table2
ON table1.matching_column = table2.matching_column
WHERE [condition];
Advantages:
- Ensures all records from the left table are included.
- Useful when you want to keep all data from the primary table regardless of matching records in the secondary table.
Disadvantages:
- Can result in large datasets, including many NULL values.
- May impact performance when dealing with very large tables.
Diagrammatic Representation :
What is Right Outer Join?
Right Outer Join returns all the rows from the table on the right and columns of the table on the left is null padded. Right Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the right table. Syntax:
SELECT [column1, column2, ....]
FROM table1
RIGHT OUTER JOIN table2 ON
table1.matching_column = table2.matching_column
WHERE [condition];
Or,
SELECT [column1, column2, ....]
FROM table1
RIGHT OUTER JOIN table2
ON table1.matching_column(+) = table2.matching_column
WHERE [condition];
Advantages:
- Ensures all records from the right table are included.
- Useful when the primary concern is to keep all data from the secondary table.
Disadvantages:
- Similar to Left Join, it can result in many NULL values, increasing the dataset size.
- Less commonly used than Left Joins, as Left Joins can achieve the same result by switching table order.
Diagrammatic Representation :
What is Full Outer Join?
Full Outer Join returns all the rows from both the table. When no matching rows exist for the row in the left table, the columns of the right table are null padded. Similarly, when no matching rows exist for the row in the right table, the columns of the left table are null padded. Full outer join is the union of left outer join and right outer join. Syntax:
SELECT [column1, column2, ....]
FROM table1
FULL OUTER JOIN table2
ON table1.matching_column = table2.matching_column
WHERE [condition];
Advantages:
- Combines the results of both Left and Right Joins.
- Useful when you need a complete view of both tables, including unmatched rows.
Disadvantages:
- Can produce very large result sets with many NULLs.
- Performance can be significantly impacted with large datasets.
Diagrammatic Representation :
Example: Consider following employee table,
| EMPID | ENAME | EMPDEPT | SALARY |
|---|---|---|---|
| 101 | Amanda | Development | 50000 |
| 102 | Diana | HR | 40000 |
| 103 | Bruce | Designing | 30000 |
| 104 | Steve | Testing | 35000 |
| 105 | Roger | Analyst | 10000 |
Department Table :
| DEPTID | DEPTNAME | LOCATION |
|---|---|---|
| 10 | Development | New York |
| 11 | Designing | New York |
| 12 | Testing | Washington |
| 13 | HelpDesk | Los Angeles |
Now,
1. Left Outer Join query -
Select empid, ename, deptid, deptname
from employee
left outer join department
on employee.empdept = department.deptname;
Output:
| EMPID | ENAME | DEPTID | DEPTNAME |
|---|---|---|---|
| 101 | Amanda | 10 | Development |
| 103 | Bruce | 11 | Designing |
| 104 | Steve | 12 | Testing |
| 102 | Diana | null | null |
| 105 | Roger | null | null |
2. Right Outer Join query -
Select empid, ename, deptid, deptname
from employee right outer join department
on employee.empdept = department.deptname;
| EMPID | ENAME | DEPTID | DEPTNAME |
|---|---|---|---|
| 101 | Amanda | 10 | Development |
| 103 | Bruce | 11 | Designing |
| 104 | Steve | 12 | Testing |
| null | null | 13 | HelpDesk |
3. Full Outer Join query -
Select empid, ename, deptid, deptname
from employee full outer join department
on employee.empdept = department.deptname;
| EMPID | ENAME | DEPTID | DEPTNAME |
|---|---|---|---|
| 101 | Amanda | 10 | Development |
| 103 | Bruce | 11 | Designing |
| 104 | Steve | 12 | Testing |
| 102 | Diana | null | null |
| 105 | Roger | null | null |
| null | null | 13 | HelpDesk |
Differences Between Left Outer Join, Right Outer Join, Full Outer Join
Criteria | Left Outer Join | Right Outer Join | Full Outer Join |
|---|---|---|---|
Definition | Returns all records from the left table and matched records from the right table, NULL for non-matching records from the right. | Returns all records from the right table and matched records from the left table, NULL for non-matching records from the left. | Returns all records when there is a match in either table, NULLs where there is no match in both tables. |
Included Rows | All rows from the left table, matching rows from the right table. | All rows from the right table, matching rows from the left table. | All rows from both tables, with NULLs for unmatched rows. |
NULL Value | NULLs appear for non-matching rows from the right table. | NULLs appear for non-matching rows from the left table. | NULLs appear for non-matching rows from both tables. |
Use Case | Use when you need all records from the left table, regardless of matches in the right. | Use when you need all records from the right table, regardless of matches in the left. | Use when you need a complete set of records from both tables, including all unmatched rows. |
Performance Impact | May increase result size with many NULLs, impacting performance. | Similar to Left Join, performance impact due to increased result size with NULLs. | Highest impact on performance due to large result set including all unmatched records. |
Common Scenarios | Retrieving all records from a primary dataset with optional matching data from a secondary table. | Retrieving all records from a secondary dataset with optional matching data from a primary table. | Combining complete data from both datasets for comprehensive results. |
Conclusion
Choosing the correct join type—Left, Right, or Full Outer Join—depends on the specific needs of your query and the data you wish to retrieve. Left Joins are ideal when you need all records from the left table, Right Joins serve when the right table is prioritized, and Full Outer Joins provide a complete dataset from both tables, including unmatched rows. Understanding these joins and their impact on data retrieval is essential for effective SQL query construction.