SQL Server LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table.

If there is no match in the right table, the result will show NULL for the right table columns.

Basic syntax:

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;

Step 1: Example tables

Employees Table

Departments Table

Step 2: Simple LEFT JOIN

Retrieve all employees with their department names, including employees without a department:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees AS e
LEFT JOIN Departments AS d
ON e.DepartmentID = d.DepartmentID;

Result:

Sara’s DepartmentID is NULL, so DepartmentName shows NULL.
IT and HR employees are matched correctly.
Finance department is not included because it has no employees.

Step 3: LEFT JOIN with table aliases

SELECT e.FirstName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

• e → Employees
• d → Departments

Result: Same as Step 3.

Table aliases make queries cleaner and easier to read, especially with multiple tables.

Step 4: LEFT JOIN with WHERE clause

You can filter results after performing the join:

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

Result:

Shows employees without a department.
Important: If you filter right table columns, use IS NULL carefully; otherwise, you might unintentionally convert LEFT JOIN into INNER JOIN.

Step 5: LEFT JOIN with multiple conditions

You can add more conditions to the ON clause:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID AND d.DepartmentName = 'IT';

Result:

Only IT departments are matched; others show NULL.