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

Table: Departments

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

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;

​

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

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;

​

Practical Use Case

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

Table: Students

Table: Courses

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;

​

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.