Inner and Outer Joins

Article · 3:00

INNER, LEFT and RIGHT - and the single mistake that silently turns an outer join into an inner one.

A join combines rows from two tables on a condition. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table, filling the right side with NULL where nothing matched - which is how you ask "all customers, with their orders if they have any". RIGHT JOIN is the mirror, and is rare in practice because swapping the table order is clearer.

There is one mistake that catches nearly everyone. Putting a condition on the right-hand table in WHERE turns a LEFT JOIN into an INNER JOIN. LEFT JOIN orders o ... WHERE o.status = 'paid' discards the unmatched rows, because their o.status is NULL and NULL = 'paid' is not true. The condition belongs in the ON clause, where it filters which rows join rather than which survive. The symptom is a report that quietly omits every customer with no orders.

Joining more than two tables chains the same idea, and the order you write them in does not determine the order they are read - the optimiser decides that. Always qualify column names with a table alias once more than one table is involved, because an unqualified ambiguous column is an error and an unqualified unambiguous one becomes an error the day the other table gains that column.

-- INNER: only customers who have at least one order.
SELECT c.name, o.reference, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT: every customer, with NULLs where there is no order.
SELECT c.name, o.reference, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- THE MISTAKE. This is an INNER JOIN wearing a LEFT JOIN's clothes:
-- a customer with no orders has o.status = NULL, which is not 'paid',
-- so the WHERE removes exactly the rows the LEFT JOIN was for.
-- SELECT c.name, o.reference
-- FROM customers c
-- LEFT JOIN orders o ON o.customer_id = c.id
-- WHERE o.status = 'paid';

-- Correct: the condition belongs in ON, deciding what JOINS, not what
-- SURVIVES.
SELECT c.name, o.reference
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

-- "Customers who have never ordered" - an anti-join.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;              -- here, IS NULL in WHERE is the point

-- Three tables, every column qualified.
SELECT c.name, o.reference, p.name AS product, oi.quantity
FROM customers c
JOIN orders      o  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
JOIN products    p  ON p.id          = oi.product_id
WHERE o.placed_at >= '2026-01-01';