SQL joins are one of those topics everyone eventually understands through repetition, but rarely gets explained clearly the first time. Here’s each join type with a concrete example against two small tables, so the difference is unambiguous.
The Setup: Two Tables
-- customers -- id | name -- 1 | Alice -- 2 | Bob -- 3 | Carol (no orders) -- orders -- id | customer_id | total -- 1 | 1 | 50.00 -- 2 | 1 | 20.00 -- 3 | 2 | 75.00 -- 4 | 99 | 30.00 (orphaned, no matching customer)
INNER JOIN: Only Matching Rows
SELECT c.name, o.total FROM customers c INNER JOIN orders o ON c.id = o.customer_id; -- Alice, 50.00 -- Alice, 20.00 -- Bob, 75.00 -- (Carol excluded: no orders. Order #4 excluded: no matching customer.)
LEFT JOIN: Everything from the Left Table
SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; -- Alice, 50.00 -- Alice, 20.00 -- Bob, 75.00 -- Carol, NULL <- kept, even with no matching order
RIGHT JOIN: Everything from the Right Table
SELECT c.name, o.total FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id; -- Alice, 50.00 -- Alice, 20.00 -- Bob, 75.00 -- NULL, 30.00 <- the orphaned order, kept even with no matching customer
FULL OUTER JOIN: Everything from Both
SELECT c.name, o.total FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id; -- Every row from both examples above, combined, with NULLs on either side -- where there's no match. (MySQL lacks native FULL OUTER JOIN — -- emulate it with a LEFT JOIN UNION a RIGHT JOIN.)
The One-Sentence Summary
INNER keeps only matches, LEFT/RIGHT keep everything from one named side plus matches from the other (filling gaps with NULL), and FULL OUTER keeps everything from both sides — the direction of “LEFT” or “RIGHT” always refers to which table is written first in the query.