SQL JOIN explained: LEFT vs INNER

Start with row pairing, not circles. For each row from the first table, the database looks for every row in the second table that satisfies the link. One customer with two orders therefore becomes two result rows. An inner join keeps only successful pairings.

-- 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)

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.)

A left join promises to keep every row from the table named first, even when no partner exists. Missing columns from the second table are filled with null. To choose the order, say the request aloud: every user and their orders means users must come first.

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

To find customers who never ordered, keep all customers with a left join, then select the rows where the order side is missing. The failure symptom to remember is a missing customer after adding a filter on orders. Move that filter into the join condition so unmatched customers survive.

-- "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

A three-table join is not a new idea. Match one table, then match another onto the wider result. Use short aliases so the links stay visible. Right joins add a second mental model for no benefit; swap the table order and write a left join instead.

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.

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.