Joins
INNER, LEFT, RIGHT and FULL joins explained with row counts — plus the accidental fan-out that ruins aggregates.
The four joins
| Join | Keeps |
|---|---|
INNER JOIN | Rows matching on both sides |
LEFT JOIN | All left rows, with NULLs where no match |
RIGHT JOIN | All right rows — rare; rewrite as LEFT |
FULL OUTER JOIN | All rows from both sides |
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- customers with no orders: anti-join idiom
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;⚠️
Filtering a left-joined table in
WHERE turns it into an inner join — rows with no match have NULL, and NULL = 'x' is never true. Put that condition in the ON clause instead.Row multiplication
One-to-many joins multiply rows. Joining orders to order_items and summing an order-level column will double-count — the most common reporting bug.
-- wrong: p.price counted once per item row
SELECT SUM(p.price)
FROM products p JOIN items i ON i.product_id = p.id;
-- right: aggregate each level separately
SELECT SUM(t.total)
FROM (
SELECT order_id, SUM(quantity * unit_price) AS total
FROM items GROUP BY order_id
) t;Self joins and CTEs
-- employees and their managers, same table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- clearer for multi-step logic
WITH monthly AS (
SELECT DATE_TRUNC('month', created_at) AS m, SUM(total) AS revenue
FROM orders GROUP BY 1
)
SELECT m, revenue FROM monthly ORDER BY m;FAQ
LEFT or INNER?
Start with INNER when matches are required. Reach for LEFT when you must keep the primary rows and show absence as NULL — including the anti-join pattern for 'rows with nothing'.
How do I join on multiple columns?
Chain conditions in
ON with AND; a composite index covering both columns keeps it fast.Related
Aggregation and GROUP BY SELECT: reading data
Last refreshed 2026-09-17.