LEFT JOIN in SQL

A LEFT JOIN (or LEFT OUTER JOIN) in SQL is used to combine rows from two or more tables based on a related column between them. It returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table. This type of join ensures that all records from the left table are included in the result set, regardless of whether they have a corresponding match in the right table.

Key Points

1. All Rows from Left Table: LEFT JOIN returns all rows from the left table, even if there are no matching rows in the right table.
2. Matching Rows from Right Table: If there is a match, the corresponding rows from the right table are included.
3. NULL Values for Non-Matching Rows: If there is no match, NULL values are returned for columns from the right table.
4. Alias Support: You can use table aliases to simplify queries.

Syntax

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

• column1, column2, ...: The columns you want to retrieve.
• table1: The left table (all rows from this table are included).
• table2: The right table (only matching rows from this table are included).
• common_column: The column that relates the two tables.

Examples

Suppose you have two tables: Employees and Departments.

Table: Employees

EmployeeID	Name	City	DepartmentID
1	Anand	Chennai	101
2	Bala	Coimbatore	102
3	Kavitha	Karaikal	103
4	Raj	Madurai	104
5	Kumar	Trichy	NULL

Table: Departments

DepartmentID	DepartmentName
101	HR
102	Finance
103	IT
104	Marketing

Query To retrieve all employees along with their department names (if available):

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	NULL

Notice that Kumar does not have a department, so the DepartmentName is NULL.

Using LEFT JOIN with WHERE Clause

You can combine LEFT JOIN with the WHERE clause to filter the results further. For example, retrieve employees who do not belong to any department.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;

Name	DepartmentName
Kumar	NULL

Using LEFT JOIN with Multiple Tables

You can use LEFT JOIN to join more than two tables. Suppose you have an additional table named Projects.

Table: Projects

ProjectID	ProjectName	DepartmentID
201	Project A	101
202	Project B	102
203	Project C	103

To retrieve all employees, their department names, and the projects they are working on (if available):

SELECT Employees.Name, Departments.DepartmentName, Projects.ProjectName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
LEFT JOIN Projects ON Departments.DepartmentID = Projects.DepartmentID;

Name	DepartmentName	ProjectName
Anand	HR	Project A
Bala	Finance	Project B
Kavitha	IT	Project C
Raj	Marketing	NULL
Kumar	NULL	NULL

Practical Use Case

Suppose you have a table named Students and another table named Courses.

Table: Students

StudentID	Name	City	CourseID
1	Ram	Chennai	101
2	Karthik	Coimbatore	102
3	David	Bangalore	NULL

Table: Courses

CourseID	CourseName
101	Mathematics
102	Science
103	English

To retrieve all students along with the courses they are enrolled in (if available):

SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.CourseID = Courses.CourseID;

Name	CourseName
Ram	Mathematics
Karthik	Science
David	NULL

Key Takeaways

1. LEFT JOIN returns all rows from the left table and matching rows from the right table.
2. If there is no match, NULL values are returned for columns from the right table.
3. It ensures that all records from the left table are included in the result set.
4. You can use LEFT JOIN with the WHERE clause to filter results further.
5. LEFT JOIN can be used to join multiple tables in a single query.