Summary
- Use LEFT JOIN when the primary focus is on ensuring all rows from the left table are included.
- Use RIGHT JOIN when the primary focus is on ensuring all rows from the right table are included.
In SQL, LEFT JOIN and RIGHT JOIN are types of OUTER JOIN used to combine rows from two tables based on a related column. The main difference lies in which table’s data is prioritized when no matching rows are found in the other table.
1. LEFT JOIN
- Definition: The LEFT JOIN (or LEFT OUTER JOIN) retrieves all rows from the left table (Table A), even if there is no match in the right table (Table B). If no match is found, the result contains NULL for columns of the right table.
- Focus: All rows from the left table are included, regardless of whether a match exists in the right table.
Syntax:
SELECT columns
FROM tableA
LEFT JOIN tableB
ON tableA.common_column = tableB.common_column;
Example:
Consider two tables:
Table A (Employees):
EmployeeID | Name | DepartmentID |
---|---|---|
1 | Alice | 10 |
2 | Bob | 20 |
3 | Charlie | NULL |
Table B (Departments):
DepartmentID | DepartmentName |
---|---|
10 | HR |
30 | IT |
Query:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result:
Name | DepartmentName |
---|---|
Alice | HR |
Bob | NULL |
Charlie | NULL |
2. RIGHT JOIN
- Definition: The RIGHT JOIN (or RIGHT OUTER JOIN) retrieves all rows from the right table (Table B), even if there is no match in the left table (Table A). If no match is found, the result contains NULL for columns of the left table.
- Focus: All rows from the right table are included, regardless of whether a match exists in the left table.
Syntax:
SELECT columns
FROM tableA
RIGHT JOIN tableB
ON tableA.common_column = tableB.common_column;
Example:
Using the same tables as above:
Query:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result:
Name | DepartmentName |
---|---|
Alice | HR |
NULL | IT |
Key Differences Between LEFT JOIN and RIGHT JOIN
Aspect | LEFT JOIN | RIGHT JOIN |
---|---|---|
Priority Table | Includes all rows from the left table. | Includes all rows from the right table. |
Unmatched Rows | NULLs are placed in columns from the right table for unmatched rows. | NULLs are placed in columns from the left table for unmatched rows. |
Focus | Retains all rows of the left table, regardless of matches. | Retains all rows of the right table, regardless of matches. |
Use Case | Use when you need all rows from the left table. | Use when you need all rows from the right table. |
Visual Representation
Imagine two overlapping circles:
- The LEFT JOIN retrieves the left circle (Table A) and the overlapping area.
- The RIGHT JOIN retrieves the right circle (Table B) and the overlapping area.