SwiftGrasp · SQL

SQL JOIN explained: LEFT vs INNER

Forget Venn diagrams. A JOIN pairs rows and glues them side by side. The only question that separates the join types: what happens to a left row with no match? INNER drops it. LEFT keeps it with NULLs.

The setup (two tables, one link)

-- users                        -- orders
-- id | name                    -- id | user_id | amount
-- 1  | Ada                     -- 1  | 1       | 19.99
-- 2  | Lin                     -- 2  | 1       | 5.00
-- 3  | Sam                     -- 3  | 2       | 42.50
--          (Sam has no orders — watch what happens to Sam)

INNER JOIN — only the matches

SELECT users.name, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

-- Ada | 19.99
-- Ada |  5.00      ← Ada matched twice → appears twice
-- Lin | 42.50
-- (no Sam. no match = no row. that's INNER.)

LEFT JOIN — keep every left row

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

-- Ada | 19.99
-- Ada |  5.00
-- Lin | 42.50
-- Sam | NULL       ← kept! right side filled with NULL

Which table is "left"? The one after FROM. "Every user and their orders" → users on the left. "Every order and its user" → orders on the left. Say the sentence; the sentence picks the table.

The classic: rows with NO match

-- "users who never ordered anything"
SELECT users.name
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;      -- the missing match IS the filter
-- → Sam

The bug that eats afternoons
Add a normal condition on the right table to a LEFT JOIN's WHERE — WHERE orders.amount > 10 — and your NULL rows vanish: NULL > 10 isn't true, so Sam is filtered out, and the LEFT JOIN silently behaves like INNER. Fix: put right-table conditions in the ON clause — ON orders.user_id = users.id AND orders.amount > 10 — filtering the matches while keeping matchless rows.

Three tables (it's just twice)

SELECT u.name, o.amount, p.title
FROM orders o
JOIN users    u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;
-- each JOIN glues one more table onto the widening row.
-- short aliases (o, u, p) keep it readable.

The rest of the family, honestly

RIGHT JOIN   -- LEFT JOIN with the tables swapped. almost nobody
             -- writes it; rewrite as LEFT and keep one mental model.
FULL JOIN    -- keep unmatched rows from BOTH sides. rare; audits.
CROSS JOIN   -- every pairing (no ON). sizes × colors = variants.

📚 One page of our SQL series. The full crash course — NULL traps, GROUP BY, the UPDATE horror story, with audio: SQL in 10 Minutes →