How Does LEFT JOIN Keep Unmatched Rows?

A LEFT JOIN preserves every row from the left table. For each one, it looks for matching rows on the right; if there are matches, it produces one output row per match, and if there are none, it produces a single output row with NULLs in every column of the right table.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

SELECT c.id, c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Four rows come back. Ada Lovelace appears twice because she has two matching orders, Grace Hopper once, and Alan Turing once with NULL in the order_id and amount columns. Every customer survives; the missing order is represented as NULLs rather than as an absent row.

Two consequences of that design. The row count is no longer the number of left rows — a left row with three matches produces three output rows — so any aggregate applied afterwards counts joined rows, not left rows. And the NULLs are real NULLs, so every predicate you write against the right-hand columns has to account for them: WHERE o.amount > 100 silently drops the unmatched rows, and WHERE o.order_id IS NULL is the way to find the rows that found no partner at all.

Also worth knowing that the LEFT JOIN is not symmetric in how it treats the two sides. Keeping the left table's rows is the guarantee; the right table can lose rows. If you need the right side preserved, write it the other way round — FROM orders o LEFT JOIN customers c keeps every order and fills in the customer name where it is missing.

Because the LEFT JOIN's guarantee — every left row survives — is implemented by filling the right-hand columns with NULLs for unmatched rows. A WHERE predicate is then evaluated against those filled-in values, and for an unmatched row the comparison is UNKNOWN, which is not TRUE, so the row is dropped. The unmatched rows are filtered out after the join has done its work, and the result is exactly what an INNER JOIN would have produced.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- The trap: only customers with a matching order survive.
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.amount > 100
ORDER BY c.name;

-- The fix: move the filter into the ON clause.
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.amount > 100
ORDER BY c.name, o.amount;

The first query returns two rows: Ada Lovelace with 120.00 and 340.50. Alan Turing is gone, and Grace Hopper is gone too because her only order is 75.25. The join was a LEFT JOIN but the result is indistinguishable from an INNER JOIN.

The second query returns three rows: Ada Lovelace with 120.00, Ada Lovelace with 340.50, and Alan Turing with NULL. Moving the predicate into the ON clause means it is applied while the join is being built, so the unmatched left row is never generated with a matching partner in the first place and survives with its NULLs intact.

The rule is simple: conditions that decide whether a right-hand row is eligible to match belong in the ON clause; conditions that decide whether an output row is wanted belong in WHERE. If you want to keep only unmatched left rows, the predicate is WHERE o.id IS NULL, and that is the anti-join pattern from the other side.