LEFT JOIN in SQL

Documentation Index

Fetch the complete documentation index at: https://rajanand.org/llms.txt

Use this file to discover all available pages before exploring further.

​

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

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

​

Using LEFT JOIN with WHERE Clause

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

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

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.