SQL Joins, Actually Explained

Tutorial · Databases

Every join answers the same question — "how do rows in one table relate to rows in another?" — but each variant answers it differently when a match is missing. Seeing the actual rows makes the difference obvious.

The setup

-- customers
id | name
1  | Amara
2  | Kofi
3  | Lin

-- orders
id | customer_id | total
1  | 1           | 42.00
2  | 1           | 15.50
3  | 4           | 8.00

Notice customer 3 (Lin) has no orders, and order 3 belongs to customer_id 4, who doesn't exist in customers. These gaps are exactly what different joins handle differently.

INNER JOIN: only the matches

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Returns only rows where both sides match: Amara's two orders. Lin disappears (no orders), and the orphan order for customer_id 4 disappears too.

LEFT JOIN: everything on the left, matched or not

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Every customer appears at least once. Lin appears with NULL for total, since she has no matching order. This is the join you reach for when you need "all customers, plus their orders if any."

RIGHT JOIN: the mirror image

RIGHT JOIN keeps everything from the right-hand table instead. It's used far less often in practice, since you can always rewrite it as a LEFT JOIN by swapping table order — most style guides prefer that for consistency.

FULL OUTER JOIN: everything, matched or not

SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Keeps Lin (no order) AND the orphan order for customer_id 4 (no customer), filling the missing side with NULL in each case. Not every database supports this directly — MySQL requires simulating it with a UNION of a LEFT and RIGHT join.