SQL JOINs Explained

A JOIN combines rows from two tables based on a related column between them, letting you query across the relationships covered in the previous lesson. The four join types differ only in which unmatched rows they keep. Getting this right matters — it's one of the most commonly confused topics in SQL.

Work through all four against the same two tables:

Note: Linus (id 3) has no orders, and one order (id 3) references a customer_id of 99, which doesn't exist in customers — deliberately, to make each join's behavior on unmatched rows visible.

INNER JOIN: only rows that match on both sides

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

Only rows where the join condition finds a match in both tables survive. Linus (no orders) and the orphaned order for customer 99 (no matching customer) are both dropped entirely. JOIN alone (without a qualifier) means INNER JOIN — it's the default.

LEFT JOIN: every row from the left table, matched or not

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

Every row from customers (the "left" table, the one named in FROM) appears at least once, regardless of whether it has a matching order. Grace and Linus have no orders, so their total column comes back NULL rather than the row disappearing. This is the join you reach for when you need "all of table A, plus whatever matches from table B if it exists" — like "every customer, with their order total if they have one."

RIGHT JOIN: every row from the right table, matched or not

SELECT customers.name, orders.total
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

The mirror image of LEFT JOIN: every row from orders (the "right" table) appears, even the one with no matching customer — name comes back NULL for it. RIGHT JOIN is used far less often in practice than LEFT JOIN, purely because you can always rewrite one as the other by swapping which table you list first — most style guides prefer sticking to LEFT JOIN for consistency rather than mixing both directions in the same codebase.

FULL OUTER JOIN: every row from both sides

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

The union of LEFT JOIN and RIGHT JOIN: every row from both tables, with NULL filled in on whichever side has no match. Note: MySQL has no FULL OUTER JOIN keyword at all — it's emulated there with LEFT JOIN UNION RIGHT JOIN. PostgreSQL and SQL Server support it directly.

Summary

| Join type | Keeps unmatched rows from... | |---|---| | INNER JOIN | Neither side | | LEFT JOIN | Left table only | | RIGHT JOIN | Right table only | | FULL OUTER JOIN | Both sides |

The next lesson covers subqueries — queries nested inside other queries, for cases a single join can't express cleanly.