JOINs, and the rows you did not expect

A join is a filter over every pair of rows

The mental model that makes joins predictable: the database conceptually pairs every row of the left table with every row of the right one, then keeps the pairs where the condition holds.

SELECT e.name, d.name
FROM employees e
JOIN departments d ON d.id = e.department_id;

No real engine does it that way — it uses an index or a hash table — but the result is defined as if it did. Two consequences follow from that sentence, and they are the two things people get wrong.

The result can have more rows than either table

If one employee belongs to one department, the join above returns one row per employee. But if a row on the left matches several rows on the right, it is repeated once per match:

SELECT o.id, i.product_id
FROM orders o
JOIN order_items i ON i.order_id = o.id;

An order with three items produces three rows. That is correct, and it is also the bug behind almost every wrong total in a report:

-- Wrong: o.total is counted once per item
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.name;

The third table multiplied the rows before SUM ever ran. Aggregate first in a subquery, or join only what you need — but notice it before the number reaches a dashboard, because nothing here errors.

INNER drops, LEFT keeps

INNER JOIN returns only the pairs that matched. LEFT JOIN returns every row of the left table, filling the right side with NULL when nothing matched.

SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;
-- An empty department still appears, with NULL for the employee.

Which gives the standard way to find rows with no match at all:

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

WHERE after a LEFT JOIN undoes it

This is the one worth memorising. These two queries look almost identical and answer different questions:

-- Every department, with its 2024 employees (or NULL)
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e
  ON e.department_id = d.id
  AND e.hired_at >= '2024-01-01';

-- Only departments that have a 2024 employee — an INNER JOIN in disguise
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.hired_at >= '2024-01-01';

The second one filters after the join has produced its NULL rows, and NULL >= '2024-01-01' is not true, so every unmatched department disappears. The rule: a condition about the right table belongs in ON; a condition about the left table belongs in WHERE.

A table can join to itself

Nothing about a join requires two tables. Aliases are what make it work:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

LEFT rather than INNER, or the one person with no manager — usually the one you were asked about — silently vanishes.