In SQL, joins are used to combine rows from two or more tables based on related columns. The LEFT JOIN and RIGHT JOIN are types of outer joins that determine which table’s rows are preserved when there is no matching row in the other table.
LEFT JOIN (or LEFT OUTER JOIN):
Returns all rows from the left table and the matched rows from the right table.
If there’s no match, the result is NULL on the side of the right table.
RIGHT JOIN (or RIGHT OUTER JOIN):
Returns all rows from the right table and the matched rows from the left table.
If there’s no match, the result is NULL on the side of the left table.
Key Differences:
The primary difference lies in which table’s rows are fully preserved:
LEFT JOIN preserves all rows from the left table.
RIGHT JOIN preserves all rows from the right table.
They can often be interchanged by switching the order of the tables.
Example:
Consider two tables:
Employees:
EmployeeID Name DepartmentID
1 Alice 10
2 Bob 20
3 Charlie NULL
Departments:
DepartmentID DepartmentName
10 HR
20 IT
30 Finance
LEFT JOIN Query:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Result:
Name DepartmentName
Alice HR
Bob IT
Charlie NULL
RIGHT JOIN Query:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Result:
Name DepartmentName
Alice HR
Bob IT
NULL Finance
In the LEFT JOIN example, all employees are listed, including “Charlie,” who isn’t assigned to any department (DepartmentID is NULL). In the RIGHT JOIN example, all departments are listed, including “Finance,” which has no employees assigned.
For a more detailed explanation, you can refer to the article on GeeksforGeeks.
Leave a comment