INNER JOIN combines rows from two tables where the join condition matches, discarding any row from either side that has no match. LEFT JOIN keeps every row from the left table regardless of a match, filling the right table's columns with NULL where there's no match; RIGHT JOIN does the mirror image; FULL OUTER JOIN keeps unmatched rows from both sides at once.
INNER JOIN: only the matches
A customer with zero orders simply never appears in this result — INNER JOIN requires a match on both sides.
SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
-- customers with NO orders are absent from this result entirelyLEFT JOIN: keep everything on the left, even without a match
This is the most commonly used outer join in practice — finding "customers with no orders" (WHERE orders.id IS NULL after a LEFT JOIN) is a classic, widely used pattern.
SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
-- customers with no orders DO appear, with orders.total as NULL
-- find customers with ZERO orders:
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;RIGHT JOIN and FULL OUTER JOIN
RIGHT JOIN is the mirror of LEFT JOIN and is rarely used in practice, since swapping the table order and using LEFT JOIN achieves the same result more readably. FULL OUTER JOIN keeps unmatched rows from BOTH sides simultaneously — note SQLite and older MySQL versions don't support FULL OUTER JOIN natively, requiring a UNION of a LEFT and RIGHT join as a workaround.
SELECT customers.name, orders.total
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;
-- includes customers with no orders AND orders with no matching customer