Keeping the rows that did not match, and the WHERE clause that throws them away again.

LEFT JOIN and Outer Joins

An INNER JOIN keeps only matches. A LEFT JOIN keeps every row from the left table, matched or not, filling the right-hand columns with NULL when nothing matched.

SQL
SELECT d.name AS department, COUNT(e.emp_id) AS staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.name
ORDER BY staff DESC;

Four departments, including the one with nobody in it. An INNER JOIN here would return three rows and the empty department would silently not exist — the report would show every department fully staffed.

Note COUNT(e.emp_id), not COUNT(*). The empty department still produces one row from the join, so COUNT(*) would report it as having one employee. Counting a column from the right-hand table counts the NULL as nothing, which is what you meant. This is the most common bug in LEFT JOIN + GROUP BY code.

Finding rows with no match

LEFT JOIN … WHERE <right side> IS NULL is the standard "what is missing?" query, and it is worth recognising on sight:

SQL
SELECT d.name AS department_with_no_staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

The join keeps everything; the WHERE then keeps only the rows where the match failed. Applied to orders, the same shape finds data-quality problems:

SQL
SELECT o.order_id, o.status, o.amount
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
ORDER BY o.order_id;

Three unpaid orders — two pending, one cancelled. And the same query against customers finds the order whose customer does not exist at all:

SQL
SELECT o.order_id, o.customer_id AS missing_customer
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

The trap: WHERE on the outer table undoes the join

This is the single most common outer-join bug, and it does not look like a bug.

SQL
SELECT COUNT(*) AS rows_returned
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.active = 1;

Eight rows — the empty department has vanished. LEFT JOIN filled its e.active with NULL, NULL = 1 is unknown, and WHERE dropped the row. A WHERE condition on the right-hand table turns a LEFT JOIN back into an INNER JOIN.

Put the condition in ON instead, where it filters what is joined rather than what survives:

SQL
SELECT COUNT(*) AS rows_returned
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id AND e.active = 1;

Nine rows, and the empty department is still there. For an inner join these two placements are equivalent; for an outer join they answer different questions:

ON — "match me only the active employees, but keep every department"
WHERE — "join everything, then discard anything that is not an active employee"

The exception is deliberate: WHERE e.emp_id IS NULL above is supposed to filter on the right-hand table, because finding the non-matches is the point.

RIGHT and FULL

RIGHT JOIN is LEFT JOIN with the tables the other way round. It exists, it is rarely used, and the reason is readability: people read a query top-down and expect the table they keep to be the one they started from. Swap the table order and use LEFT.

FULL OUTER JOIN keeps unmatched rows from both sides. It is the right tool for reconciling two systems — "what is in A but not B, and B but not A, in one result". PostgreSQL, SQL Server and Oracle support it; MySQL and SQLite do not, where the workaround is a LEFT JOIN and a RIGHT JOIN combined with UNION.

Chaining outer joins

Once a join is LEFT, every join after it in the chain usually needs to be LEFT too. A single inner join further down will discard the NULL-filled rows the first one worked to preserve, and the query looks fine.

SQL
SELECT c.name AS customer, o.order_id, p.method
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.customer_id
LEFT JOIN payments p ON p.order_id    = o.order_id
ORDER BY c.customer_id, o.order_id;

Every customer appears, including the two who have never ordered, and every order appears whether or not it was paid.

Common mistakes

COUNT(*) after a LEFT JOIN — counts unmatched rows as one.
A WHERE on the right-hand table — silently converts to an inner join.
An inner join later in an outer-join chain — undoes the outer join.
Reaching for RIGHT JOIN — swap the tables and stay readable.

Interview question

Your LEFT JOIN is returning the same rows as an INNER JOIN. Why?

Almost certainly a WHERE clause referencing a column from the right-hand table. The NULLs the outer join produced fail that condition and the rows are filtered out. Move the condition into ON.

Check yourself

List every customer with their order count, including customers with none.
Why does COUNT(*) overcount in that query, and what fixes it?
Rewrite A RIGHT JOIN B as a LEFT JOIN.