Thursday, January 9, 2025
HomeProgrammingDifference between Left Join and Right Join

Difference between Left Join and Right Join

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.
See also  What is the function of a monitor?

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.
See also  Map Interface in Java

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.
See also  How to Drop a Table If It Exists in SQL Server

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.

 

Previous article
Next article
RELATED ARTICLES
0 0 votes
Article Rating

Leave a Reply

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
- Advertisment -

Most Popular

Who is Sa Nguyen?

Helldivers

Who is ExtraEmily?

Recent Comments

0
Would love your thoughts, please comment.x
()
x