SQL Rows
Show me the rows
SQL joins with the actual data, not Venn diagrams. Pick a join and watch it test every pair. See exactly which rows survive, which multiply, and which come back NULL. Then walk through the traps that catch everyone, and on into GROUP BY, CASE and window functions.
How this works, and how it's checked
Every query on this page runs in your browser through a small SQL engine written for this page. A real database returns the result and throws away how it got there. This one keeps a record of every pair it tested, what ON said about each, which rows it padded, and what WHERE said about every row. The figures draw that record.
To make sure the drawings tell the truth, the site's build checks run every query here through real SQLite (3.51), plus 3,000 random queries with NULLs, duplicates, all five join types, subqueries and grouping. If the engine and SQLite disagree on a single row, the check fails.
Where databases differ, this follows SQLite. Nothing on the page depends on those differences except a note on FULL JOIN, which MySQL doesn't have.
Every join starts with every pair
A join doesn't overlap two circles. It pairs every row on the left with every row on the right, then keeps the pairs that pass a test.
Four customers and five orders make twenty possible pairs. Listing all of them is a CROSS JOIN, and it's the starting point of every other join. The ON condition is a test that each pair takes: does this order's customer_id equal this customer's id?
A real database doesn't literally build all twenty. It uses indexes and hash tables to skip pairs that can't match. But the answer is always the same as if it had, so thinking in pairs is never wrong.
INNER JOIN keeps the pairs that pass
That's all it does. If no pair passes, there's no row.
The result has one row per pair that passed, not one row per customer. Ana has two orders, so two of her pairs pass and she appears twice. Dev has no orders, so none of Dev's pairs pass and Dev disappears. Order 105 has no customer, so it disappears too.
JOIN on its own means INNER JOIN. So does the older spelling with a comma: FROM customers c, orders o WHERE o.customer_id = c.id. That's every pair, then a filter, written out longhand.LEFT, RIGHT and FULL keep the leftovers
An outer join is an INNER JOIN plus the rows that never found a partner, padded with NULLs.
LEFT JOIN tests the same pairs in the same way. Then, for each left row that matched nothing, it adds one row with NULL in every right-hand column. RIGHT JOIN does the same for unmatched right rows. FULL does both.
Those NULLs aren't in your data. The join made them up to mean "no partner". Hold on to that for the WHERE trap.
The posters with seven Venn diagrams have three more: "left excluding", "right excluding" and "outer excluding". Those aren't joins. Flip the toggle to only the leftovers: each one is an outer join plus a WHERE that keeps only the padded rows.
A self join is just a join
When a table points at itself, join it to itself. Give it two names and it's two tables.
The customers get one more column here: referred_by, the id of the customer who brought them in. To put each customer's name next to their referrer's name, you need two customer rows side by side. So the table goes in twice: once as c, the customer, and once as r, the referrer.
Nothing about the join changes. Every c row is paired with every r row, itself included, and ON tests each pair. The aliases aren't optional. Without them, customers.id could mean either copy.
WITH RECURSIVE), not more joins.The WHERE trap
"Every customer, with their shipped orders if they have any." Where you put the status test changes the answer.
SQL runs the join first and WHERE after it. By the time WHERE sees Dev's row, the join has already padded it with NULLs. WHERE asks whether NULL = 'shipped'. The answer is unknown, and WHERE keeps only rows where the answer is true.
Put the same test in ON and it becomes part of the pairing. A pair that fails ON is just not a match, and a left row with no matches gets padded and kept.
Fan-out: the totals that grow
Join two one-to-many tables to the same parent and they multiply each other.
Each order pairs with each ticket from the same customer. Ana has 2 orders and 2 tickets, so she becomes 2 × 2 = 4 rows before GROUP BY runs, and SUM counts each order twice.
Nothing errors. The numbers just come out bigger, and only for the customers who have several of both.
NULL isn't a value
"Customers who have never ordered." The obvious query returns nobody.
NULL means unknown, not empty. Any comparison with an unknown is itself unknown: 4 = NULL is unknown, 4 <> NULL is unknown, and even NULL = NULL is unknown.
x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. If none of those is false but one is unknown, the whole thing is unknown, and WHERE drops the row. One NULL in the subquery and NOT IN can't be true for anyone.
GROUP BY squashes rows into buckets
One row comes out per bucket. Everything else in the SELECT has to summarize the bucket's rows.
GROUP BY sorts the rows into buckets by a value, here the customer's name. Then COUNT, SUM and the other aggregates boil each bucket down to one number. Watch what they do with NULLs: every aggregate except COUNT(*) skips them.
There are two places to filter, and they ask different questions. WHERE removes rows before they go into buckets. HAVING removes whole buckets after they're built, which is why only HAVING can look at a SUM.
COUNT(*) counts rows. COUNT(column) counts rows where that column isn't NULL. After a LEFT JOIN, that difference is the whole answer: count the right-hand table's id, or every customer seems to have at least one order.CASE: the first true WHEN wins
CASE is an if/else chain inside an expression. The order of the WHENs is part of the logic.
CASE checks each WHEN from the top, returns the THEN of the first one that's true, and stops. If none is true, you get the ELSE, or NULL if there's no ELSE. A test against NULL is never true, so a NULL always falls through to the ELSE.
Put a CASE inside an aggregate and it does more. Each row's value can be steered into a different column, and SUM adds the columns up per group. That's how you turn rows into columns (a pivot) without a PIVOT keyword.
WHEN o.amount >= 20 AND o.amount < 50.Window functions keep every row
A window computes over a set of related rows, like GROUP BY, but hands the answer back to each row instead of collapsing them.
OVER (…) describes the window. PARTITION BY says which rows belong together: GROUP BY without the squashing. ORDER BY inside OVER puts each partition in order, and then a row's window runs from the start of its partition up to that row. That's how you get a running total.
Some functions only make sense in a window. ROW_NUMBER() numbers the rows in order. RANK() gives tied rows the same number. LAG() and LEAD() read the previous or next row.
The order SQL actually runs
You write SELECT first. The database gets to it nearly last.
- FROM + JOIN … ON
- WHERE
- GROUP BY
- HAVING
- window functions
- SELECT
- DISTINCT
- ORDER BY
- LIMIT
Every trap on this page comes from that order:
- ON is tested during the pairing, WHERE after it. That's the WHERE trap.
- Joins multiply rows before GROUP BY collapses them. That's fan-out.
- WHERE runs before GROUP BY, so it can't see SUM or COUNT. That's what HAVING is for.
- Window functions run after WHERE, GROUP BY and HAVING. That's why you can't filter on ROW_NUMBER() in the same query, and have to wrap it in another one.
- SELECT runs after WHERE, so WHERE can't use a name you gave a column in SELECT. ORDER BY runs after SELECT, so it can.
Real databases reorder this freely to go faster. That's the query planner's job. But they have to return exactly what this order would return, so it's always the right way to read a query.
A join is a test on every pair. Every trap here is a row you didn't picture.