SQL joins, explained by watching the rows

Venn diagrams tell half the story. Switch join types below and watch which customers and orders make it into the result, and where the NULLs come from.

pick a join type

customers
id	name
1	Ada
2	Grace
3	Linus
4	Hedy

orders
id	customer_id
101	1
102	2
103	1
108	9

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

Only pairs where the ON condition matches. Ada appears twice because she has two orders. Linus and Hedy (no orders) and order 108 (unknown customer 9) drop out.

Result · 3 rows
name	order id	total
Ada	101	131
Ada	103	9.5
Grace	102	34.5

Run this on the full sample database

SQL join syntax

SELECT columns
FROM left_table  AS l
[INNER | LEFT | RIGHT | FULL OUTER] JOIN right_table AS r
  ON r.foreign_key = l.primary_key
[WHERE …];

Every join has three parts: the two tables, the join type, and the ON condition that says which rows belong together. The database conceptually pairs every left row with every right row (a cross join), keeps the pairs where ON is true, and then, for outer joins, adds back the unmatched rows padded with NULL.

That one mental model explains every surprise: duplicates come from multiple matching pairs, NULLs come from the “add back” step, and a missing ON gives you the full cross product.

The joins at a glance

Join	Keeps	Typical use
`INNER JOIN`	Matching pairs only	Orders with their customer
`LEFT JOIN`	All left rows + matches	All customers, with orders if any
`RIGHT JOIN`	All right rows + matches	Same as LEFT with tables swapped
`FULL OUTER JOIN`	Everything from both	Reconciling two lists
`LEFT JOIN … WHERE r.id IS NULL`	Left rows without a match	Customers who never ordered
`CROSS JOIN`	Every combination	Building a size × colour grid
Self join	A table joined to itself	Employees and their managers

Self join example

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Joining more than two tables

SELECT o.id, c.name, p.name AS product, i.qty
FROM orders o
JOIN customers   c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products    p ON p.id = i.product_id;

Try any of these in the SQL online editor; its sample database contains these exact tables. For the rest of the syntax, see the SQL cheat sheet.

If you like learning by poking at a working model like the one above, ahaboo has narrated, interactive explainers on how everyday things really work, from compound interest to why the seasons happen.

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, and fills the right table’s columns with NULL where there is no match.

Is JOIN the same as INNER JOIN?

Yes. JOIN on its own means INNER JOIN in every major database. Likewise LEFT JOIN and LEFT OUTER JOIN are identical; OUTER is optional.

Why does my join return more rows than the table has?

A join returns one row per matching pair. If one customer has three orders, that customer appears three times. Duplicate keys on both sides multiply rows; aggregate with GROUP BY or de-duplicate before joining.

Where should a filter go: ON or WHERE?

For INNER JOIN it makes no difference to the result. For LEFT JOIN it does: a condition on the right table in WHERE removes the NULL rows and silently turns it into an inner join; put it in the ON clause to keep unmatched left rows.

Does MySQL support FULL OUTER JOIN?

No. Emulate it with a LEFT JOIN UNION a RIGHT JOIN. PostgreSQL, SQL Server, Oracle and SQLite (3.39+) support FULL OUTER JOIN directly.