A JOIN combines rows from two tables using a shared key. The only thing that changes between join types is what happens to rows that don't match. The shaded circles below (A = left table, B = right table) show exactly which rows you keep.
Throughout, we use two small tables: customers (A) and orders (B), linked by orders.customer_id = customers.id. Note that some customers have no orders, and one order has no matching customer.
Join types
1. INNER JOIN
Only the matches
Keeps rows that exist in both tables. Customers with no orders and orders with no customer are dropped. This is the default and most common join.
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;2. LEFT JOIN
All of A, matched where possible
Keeps every row from the left table (customers). Where there's no matching order, the order columns come back as NULL. Perfect for "all customers, and their orders if any".
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Add WHERE o.id IS NULL to find only the customers who have never ordered, a classic "rows with no match" query.
3. RIGHT JOIN
All of B, matched where possible
The mirror image of LEFT JOIN: every row from the right table is kept, with NULLs where the left has no match. In practice most people just swap the tables and use LEFT JOIN instead.
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;4. FULL OUTER JOIN
Everything from both sides
Keeps all rows from both tables, matched where possible and NULL-padded where not. Great for reconciliation ("show me everything on either side, flag what doesn't line up"). MySQL lacks it, emulate with LEFT + UNION + RIGHT.
SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;5. CROSS JOIN
Every combination
No ON condition: pairs every row of A with every row of B (a Cartesian product). 10 rows × 10 rows = 100 rows. Useful for generating grids, e.g. every product in every size.
SELECT s.size, col.name
FROM sizes s
CROSS JOIN colours col;6. SELF JOIN
A table joined to itself
Not a separate keyword, just a table joined to itself with two aliases. Ideal for hierarchies, like matching each employee to their manager in the same employees table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Which join do I use?
| Goal | Join |
|---|---|
| Only rows that match on both sides | INNER JOIN |
| All rows from the main table + matches | LEFT JOIN |
| Rows in A that have no match in B | LEFT JOIN + WHERE b.id IS NULL |
| Everything from both, lined up | FULL OUTER JOIN |
| Every combination of two sets | CROSS JOIN |
| Relate rows within one table | SELF JOIN |
The #1 join gotcha: putting a condition on the right table in WHERE instead of ON quietly turns a LEFT JOIN back into an INNER JOIN. Filter the right table inside the ON clause to keep the unmatched left rows.