SQL joins explained, which rows survive and why

A SQL join combines rows from two tables by matching a value they share. Which join you pick decides what happens to the rows that do not match: an INNER JOIN drops them, a LEFT JOIN keeps the left table's, a RIGHT JOIN keeps the right table's, and a FULL OUTER JOIN keeps both, filling the gaps with NULL.

That is the whole concept. Everything difficult about joins comes from row counts, not from the syntax.

Which is a problem, because the picture everyone learns joins from is a Venn diagram, and a Venn diagram cannot show a row count. This post uses two small tables and the actual output instead. Every result below was produced by running the query against SQLite 3.49, so the rows you see are the rows the database returned.

The two tables

Three customers, four orders. One of those orders is a mess, deliberately.

customers

orders

Two facts to keep in mind, because every result below turns on them:

• Ada has two orders. She is one customer and two rows in orders.
• Grace has none, and order 104 belongs to customer 9, who does not exist. One unmatched row on each side.

INNER JOIN: only the matches

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

3 rows. Grace is missing because she has no orders. Order 104 is missing because it has no customer.

INNER is the default, so JOIN on its own means this. It is the join to use when a row without a match is not interesting, which covers most reporting queries.

Notice Ada appears twice already. Hold that thought.

LEFT JOIN: keep everything on the left

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

4 rows. Grace is back, with NULL in every column that came from orders.

"Left" means the table in the FROM clause, the one written first. Every one of its rows is guaranteed to appear at least once. Where there was no match, the right table's columns are NULL.

This is the join for questions shaped like "all X, with their Y if they have any". All customers with their order totals. All students with their grades. All products with their reviews. If a report is missing the rows with zero of something, a LEFT JOIN is usually the fix.

Order 104 is still gone. LEFT JOIN protects one side only.

RIGHT JOIN: keep everything on the right

SELECT c.name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

4 rows, and now the orphan shows up: order 104, 25 in value, belonging to nobody. Grace is gone instead.

RIGHT JOIN is LEFT JOIN with the tables the other way round, and that is exactly how most people write it: swap the table order and use LEFT. Reading a query top to bottom where "left" always means "the one above" is easier on the next person.

The one thing RIGHT JOIN is genuinely good for is what happened here, finding data that should not exist. An order pointing at a missing customer is a broken foreign key, and it is the sort of thing that hides for months.

FULL OUTER JOIN: keep everything

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

5 rows: the 3 matches, plus Grace, plus order 104. Nothing is dropped from either side.

This is the reconciliation join. Two systems that should agree, and you want every disagreement in both directions.

One portability note: PostgreSQL, SQL Server, Oracle and modern SQLite all support it. MySQL does not, and the workaround there is a LEFT JOIN and a RIGHT JOIN glued together with UNION.

CROSS JOIN: every combination

SELECT COUNT(*) FROM customers CROSS JOIN orders;

12

3 customers times 4 orders. No ON clause, no matching, just every possible pairing.

You will rarely write one on purpose. It is useful for generating grids, like every product in every size. Mostly it matters because you can produce one by accident, and a CROSS JOIN between two real tables is how a query that used to take a second starts taking an hour.

Why the Venn diagram is misleading

Here is the thing the circles cannot show you.

A Venn diagram says a join is about set membership: which items are in A, in B, or in both. But joins do not work on membership. They work on row pairing, and a single row on one side can pair with many rows on the other.

Watch what happens when we select only customer columns:

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

Two identical Ada rows. We asked for customers and got a duplicate, because Ada has two orders and the join produced one row per order.

There is no region of a Venn diagram that represents "Ada, twice". The picture has no way to express it, which is why people who learned joins from circles are surprised the first time a report double counts.

And it does double count:

SELECT COUNT(*) AS customer_count
FROM customers c
JOIN orders o ON o.customer_id = c.id;

3

Three, from a table containing two matching customers. Counting rows after a join counts pairings, not customers.

The fix is to say what you actually mean:

SELECT COUNT(DISTINCT c.id) AS customer_count
FROM customers c
JOIN orders o ON o.customer_id = c.id;

2

If a total ever comes out suspiciously high after you add a join, this is why. The join multiplied your rows and the SUM obediently added the duplicates.

The WHERE clause that quietly kills your LEFT JOIN

This is the most common join bug in production code, and it produces a plausible-looking wrong answer rather than an error.

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

Grace is gone, despite the LEFT JOIN that exists specifically to keep her.

The reason is ordering. The join runs first and produces Grace's row with NULL in o.total. Then WHERE runs, and NULL > 20 is not true, so the row is filtered out. Your LEFT JOIN just became an INNER JOIN.

Move the condition into the ON clause and it applies while pairing rather than after:

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

Three rows. Grace is preserved with no matching order, which is what "all customers, plus their big orders if any" should mean.

The rule: conditions on the right-hand table of an outer join belong in ON, not WHERE. Conditions on the left table can go in either.

Finding the rows with no match

Combine the two ideas and you get one of the most useful queries there is:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Grace

Keep everything on the left, then keep only the rows where the right side came back empty. Customers who never ordered. Posts with no comments. Users who never finished onboarding.

Flip it to audit the other direction:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

There is the broken row, named exactly.

Picking a join

Then check two things before you trust the result: did the row count change more than you expected, and is anything being counted or summed after the join?

Practice on a real database

Joins are the point where SQL stops being obvious, and reading result tables is not the same as writing the query that produced them.

SQL Basics runs a real SQLite database in your browser, so you can write a join, see the rows, add a WHERE, and watch a row disappear. No install, no server, no sample database to download.

Related: How Websites Work for where the database sits in the request, and the SQL hub for the full path from SELECT onward.