SQL LEFT JOIN

A LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the "left" table in your query, plus the matching row(s) from the "right" table. When a left-table row has no match on the right, the query still keeps it — and fills every right-table column with NULL instead of dropping the row. This makes LEFT JOIN the tool of choice whenever you need to ask questions like "which customers have never ordered?" or "which departments have zero employees?" — questions an INNER JOIN simply cannot answer, because an inner join silently discards unmatched rows.

Overview: How LEFT JOIN Works

Every join starts with two tables: the one listed after FROM (the left table) and the one listed after JOIN (the right table). The database engine evaluates the ON condition for every possible pairing of a left row with a right row. With a plain JOIN (inner join), only pairings where the ON condition is true survive. A LEFT JOIN changes that rule: it guarantees every row from the left table appears in the result at least once. If one or more right-table rows satisfy the ON condition, the left row is repeated once per match, with the right columns filled in. If no right-table row satisfies the condition, the left row still appears exactly once, with every right-table column set to NULL.

Internally, the query engine (SQLite, MySQL, PostgreSQL, SQL Server all behave the same way logically) typically executes this as a nested-loop or hash-join strategy: for each row of the left table, it probes the right table for matches using the join condition. If an index exists on the right table’s join column, the engine can look up matches directly instead of scanning the whole table — this is why indexing foreign-key columns matters for join performance. When no match is found, the engine synthesizes a "phantom" row of NULLs for the right table rather than skipping the left row.

Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table
  ON left_table.key_column = right_table.key_column
WHERE condition
ORDER BY column;

Part	Meaning
left_table	The table whose rows are always preserved in full.
LEFT JOIN right_table	The table matched against; unmatched rows produce NULLs.
ON ...	The condition (usually an equality between a foreign key and a primary key) used to find matches. Evaluated during the join, before WHERE.
WHERE	Optional filter applied after the join has produced its rows — including the NULL-padded ones.

Some databases also accept the longer keyword LEFT OUTER JOIN; it is exactly the same operation as LEFT JOIN. There is no dedicated "RIGHT JOIN only" concept you need separately — a RIGHT JOIN is just a LEFT JOIN with the tables swapped, though SQLite (unlike MySQL/PostgreSQL) only added RIGHT JOIN support in more recent versions, so writing everything as LEFT JOIN is the more portable habit.

Examples

Example 1: Every department, even empty ones

Suppose we have a departments table and a staff table where each staff member belongs to one department. Using LEFT JOIN from departments to staff guarantees every department shows up, whether or not it has any staff.

SELECT departments.department_name, staff.staff_name
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id
ORDER BY departments.department_id, staff.staff_name;

Result:

department_name	staff_name
Engineering	Ana
Engineering	Ben
Sales	Carla
Marketing	Deepak
Legal	NULL

Engineering appears twice because it has two matching staff rows. Legal appears once, with NULL in place of a staff name, because no one in the staff table belongs to department 4. An INNER JOIN here would have silently dropped Legal entirely.

Example 2: Finding rows with no match

A very common pattern pairs LEFT JOIN with a WHERE ... IS NULL filter to find left-table rows that have no counterpart at all — here, departments with no staff.

SELECT departments.department_name
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id
WHERE staff.staff_id IS NULL;

Result:

department_name
Legal

Only departments whose join produced a NULL phantom row survive the WHERE staff.staff_id IS NULL filter — that is, only departments with zero matching staff. This trick works because staff_id is the staff table’s primary key and can never legitimately be NULL in a real match.

Example 3: Counting matches per left row, including zero

SELECT departments.department_name, COUNT(staff.staff_id) AS staff_count
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id
GROUP BY departments.department_id, departments.department_name
ORDER BY departments.department_id;

Result:

department_name	staff_count
Engineering	2
Sales	1
Marketing	1
Legal	0

COUNT(staff.staff_id) counts only non-NULL values of that specific column, so Legal correctly reports 0. This is the standard way to get a true zero count for unmatched groups — see the Common Mistakes section for what goes wrong if you use COUNT(*) instead.

Example 4: LEFT JOIN on a customers/orders style schema

The same pattern applies to any "parent with optional children" relationship, such as customers and their orders:

SELECT customer_name, country, order_id, amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Result: one row is produced for every customer. Customers who have placed at least one order show their order_id and amount values (a customer with multiple orders appears once per order). A customer with no matching row in orders still appears exactly once, with order_id and amount shown as NULL — that customer simply has no purchase history yet.

How It Works Step by Step

SQL reads top-to-bottom, but the engine executes a query in a fixed logical order, and understanding it explains a lot of LEFT JOIN behavior:

1. FROM — the left table’s rows are established first.
2. LEFT JOIN … ON — for each left row, the engine looks for right-table rows matching the ON condition. Matches are attached; if there are none, one row is still emitted with all right-table columns set to NULL.
3. WHERE — filters are applied to the joined result, including those NULL-padded rows. This is why filtering on a right-table column in WHERE can accidentally erase the very unmatched rows you added the LEFT JOIN to keep.
4. GROUP BY / aggregates — rows are bucketed and functions like COUNT, SUM, AVG run per group.
5. HAVING — filters applied after aggregation.
6. SELECT — the final column list is computed.
7. ORDER BY and LIMIT — sorting and row-limiting happen last.

Because the ON condition is evaluated during step 2 (before WHERE), it is the safe place to put extra matching conditions that should narrow which right-rows count as a "match" without discarding unmatched left rows. Conditions placed in WHERE instead run after the outer join has already happened, and can undo its effect — see the first mistake below.

Common Mistakes

Mistake 1: Filtering on the right table in WHERE turns LEFT JOIN back into INNER JOIN

Placing a condition on a right-table column in WHERE looks harmless, but NULL fails almost every comparison, so it quietly strips out the unmatched rows you were trying to keep:

SELECT departments.department_name, staff.staff_name
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id
WHERE staff.staff_id > 0;

Legal has no staff, so its staff.staff_id is NULL. The comparison NULL > 0 evaluates to unknown/false, not true, so the WHERE clause removes the Legal row — the query behaves exactly like an INNER JOIN, defeating the purpose of the LEFT JOIN. The fix is to move the extra condition into the ON clause, where it only affects which rows count as a match, not whether the left row survives:

SELECT departments.department_name, staff.staff_name
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id AND staff.staff_id > 0
ORDER BY departments.department_id;

Now Legal still appears (with a NULL staff name), because the extra condition only shaped the join itself instead of filtering the final result.

Mistake 2: Using COUNT(*) instead of COUNT(column) to count matches

SELECT departments.department_name, COUNT(*) AS staff_count
FROM departments
LEFT JOIN staff ON departments.department_id = staff.department_id
GROUP BY departments.department_id, departments.department_name
ORDER BY departments.department_id;

This reports Legal as having 1 staff member, not 0, because COUNT(*) counts rows regardless of their content — and the outer join still produced one NULL-padded row for Legal. The fix is to count a specific column that is guaranteed to be NULL only on unmatched rows, such as the right table’s primary key: COUNT(staff.staff_id), as shown earlier in Example 3, which correctly reports 0.

Best Practices

• Always ask "which table must keep every row?" first — that table goes in the FROM clause (or leftmost position), and the optional/dependent table follows LEFT JOIN.
• Put filters that should narrow the match in the ON clause; put filters that should narrow the final result set in WHERE — know which one you mean.
• When counting or aggregating a right-table column after a LEFT JOIN, use COUNT(column), not COUNT(*), so unmatched groups correctly show zero.
• Use WHERE right_table.key IS NULL after a LEFT JOIN as the standard idiom for "rows in A with no match in B".
• Index the foreign-key column used in the ON clause (typically on the right table) to keep large outer joins fast.
• Always qualify column names with the table name once you join two or more tables, especially any column name that exists in both tables, to avoid ambiguous-column errors.

Practice Exercises

1. Using the customers and orders sample tables, write a LEFT JOIN query that lists every customer’s name alongside their order amount, so customers with no orders still appear (with NULL amounts).
2. Extend the query from Exercise 1 so it only returns customers who have never placed an order. (Hint: filter on a column from orders being IS NULL.)
3. Using the departments/staff example schema from this lesson, write a query that returns each department’s name and its staff count using LEFT JOIN and GROUP BY, making sure departments with zero staff correctly show 0 rather than being omitted.

Summary

• LEFT JOIN keeps every row from the left table, filling unmatched right-table columns with NULL.
• The ON clause runs during the join; WHERE runs after — filtering a right-table column in WHERE can accidentally turn a LEFT JOIN into an INNER JOIN.
• WHERE right_table.column IS NULL after a LEFT JOIN is the standard way to find unmatched left-table rows.
• Use COUNT(column), not COUNT(*), when you need accurate zero counts for unmatched groups.
• LEFT OUTER JOIN is identical to LEFT JOIN; the OUTER keyword is optional.