SQL Left Join

An SQL LEFT JOIN (also known as a LEFT OUTER JOIN) retrieves all records from the left table and the matching records from the right table. If there is no match for a row from the left table, the columns belonging to the right table will display NULL values in the final output.

The Core Concept

Left Table: The table specified immediately after the FROM clause.
Right Table: The table specified after the LEFT JOIN keyword.
The Rule: You will never lose data from the left table. You will only pull matching information from the right table.

Basic Syntax

SELECT column_list
FROM table_left
LEFT JOIN table_right 
  ON table_left.common_column = table_right.common_column;

The OUTER keyword is optional; LEFT JOIN and LEFT OUTER JOIN function identically.

Step-by-Step Example

Imagine you manage a store database containing an Employees table and a Departments table:

The Source Data

Employees Table (Left)

employee_id	employee_name	department_id
1	Alice	10
2	Bob	20
3	Charlie	NULL

Departments Table (Right)

department_id	department_name
10	Engineering
20	Marketing
30	Finance

The Query

To get a full list of employees alongside their respective department names, execute the following:

SELECT e.employee_id, e.employee_name, d.department_name
FROM Employees e
LEFT JOIN Departments d 
  ON e.department_id = d.department_id;

The Result Set

employee_id	employee_name	department_name
1	Alice	Engineering
2	Bob	Marketing
3	Charlie	NULL

Why this matters:

Alice and Bob find direct matches.
Charlie does not have a department assigned, but because this is a LEFT JOIN, his record is preserved while the missing department name safely defaults to NULL.
The Finance department does not appear because no employee belongs to it, and it sits in the right table.

Left Join vs. Inner Join

The easiest way to understand a LEFT JOIN is to contrast it directly with an INNER JOIN:

Feature	INNER JOIN	LEFT JOIN
Unmatched Left Rows	Excluded from the final output.	Included (Right side fills with NULL).
Unmatched Right Rows	Excluded from the final output.	Excluded from the final output.
Primary Use Case	When you require records that exist in both tables.	When you need a complete master list from the primary table, regardless of auxiliary matches.

Pro-Tip: Finding Missing Records (Anti-Join)

You can leverage a LEFT JOIN to uncover gaps or missing entries in your datasets by targeting the NULL values in a WHERE clause:

-- Find all employees who do not belong to any department
SELECT e.employee_name
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Critical Trap: The WHERE Clause Filter

A very common mistake when using a LEFT JOIN is filtering columns from the right table inside a standard WHERE clause. Doing this accidentally converts your LEFT JOIN back into a strict INNER JOIN, dropping unmatched rows.sql

-- ❌ INCORRECT (Drops 'Amit' because NULL cannot equal 'Marketing')
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_name = 'Marketing';

--  CORRECT (Move the right-side table criteria up into the ON clause)
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.department_id 
  AND d.department_name = 'Marketing';