Outer Joins and Join Pitfalls

Chapter 10 paired customers with orders and quietly discarded everyone who had never ordered. Chapter 11 of the SQL for Beginners series keeps them: the LEFT JOIN returns every row from the left table whether it matched or not, filling the right-hand columns with NULL. That single word — NULL — is where most join bugs live, so this chapter works through the classic wrong-results trap, the IS NULL anti-join, the support story for RIGHT and FULL OUTER JOIN, and the row-multiplying fan-out that inflates totals.

Keeping unmatched rows

Change nothing except the keyword. LEFT JOIN (written LEFT OUTER JOIN by people who like emphasis) walks the left table and keeps each row, matched or not:

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

Expected output:

id  first_name  order_id  status
--  ----------  --------  ---------
1   Aarav       1         paid
1   Aarav       2         shipped
2   Priya       3         cancelled
3   Lena        4         shipped
4   Marc
5   Wei         5         paid
6   Amara       6         paid

Seven rows for six customers, because Aarav has two orders. Marc is still here, with blank cells — the blanks are NULL, printed as nothing by sqlite3, and they mean “this customer has no order”. Every row from the left table appears at least once; rows from the right table appear only where they matched. That is the whole contract, and the null padding is the part you must design your queries around.

The NULL trap: why your LEFT JOIN returns the wrong rows

Here is the bug that has embarrassed every analyst at least once. You want all customers except those with a cancelled order, so you add a filter:

SELECT c.id, c.first_name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'
ORDER BY c.id;

Expected output:

id  first_name  order_id  status
--  ----------  --------  -------
1   Aarav       1         paid
1   Aarav       2         shipped
3   Lena        4         shipped
5   Wei         5         paid
6   Amara       6         paid

Marc is gone, and so is Priya. Neither was cancelled — Marc has no order at all, and Priya has nothing else. The reason is three-valued logic: in that padded row o.status is NULL, and NULL <> 'cancelled' is not true, it is NULL, and WHERE keeps only true. A filter on a right-hand column silently deletes every null-padded row. The fix is to decide where the condition belongs — if you meant “exclude cancelled orders but keep all customers”, the condition belongs in ON:

SELECT c.first_name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
ORDER BY c.id;

Expected output:

first_name  order_id  status
----------  --------  -------
Aarav       1         paid
Aarav       2         shipped
Priya
Lena        4         shipped
Marc
Wei         5         paid
Amara       6         paid

Seven rows again: Priya and Marc both survive, with the cancelled order simply not matched. Conditions in ON decide which right-hand rows are eligible; conditions in WHERE decide which result rows are reported. When you truly want the filtered row gone but the customer kept, write the test against the null itself: WHERE o.status IS NULL OR o.status <> 'cancelled' keeps every non-cancelled order plus every customer who has none.

Anti-joins: finding what is missing

The same padding answers the question “which customers have never ordered?”. Left join, then keep the rows where the right side came back NULL:

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

Expected output:

first_name  last_name
----------  ---------
Marc        Tremblay

Test o.id, the right table’s primary key, rather than a nullable detail column: it is NULL exactly when nothing matched. This shape — left join plus IS NULL — is called an anti-join, and it is how you find orphans, lapsed customers and missing invoices. Note in passing that COUNT(*) and COUNT(o.id) disagree on padded rows: COUNT(*) counts Marc’s fake row as 1 while COUNT(o.id) correctly answers 0, which is why every left-joined count in this chapter uses the right-hand column.

RIGHT and FULL OUTER JOIN

The mirror image of a left join keeps the right table instead: FROM orders o RIGHT JOIN customers c ON c.id = o.customer_id hands back every customer whether they ordered or not, producing the same seven rows as the left join above with Marc’s order id blank. The fully symmetric FULL OUTER JOIN keeps unmatched rows from both sides. Both run on SQLite 3.39 and later — but support is uneven, and this is worth memorising before you copy a join from a blog post:

• SQLite — gained RIGHT and FULL OUTER only in version 3.39 (2022); every older build answers Error: near "RIGHT": syntax error, and plenty of servers, hosted tools and language bindings still ship one of those builds.
• MySQL — has supported RIGHT JOIN forever but FULL OUTER JOIN only since 8.0.18, and even then not in every context.
• PostgreSQL — supports both, always has.

The portable answer is to write the join in the other direction. A full outer join is a left join plus the rows a left join in the reverse direction would find, joined with UNION ALL:

SELECT c.id, c.first_name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.id, c.first_name, o.id AS order_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY 1;

Expected output:

id  first_name  order_id
--  ----------  --------
1   Aarav       1
1   Aarav       2
2   Priya       3
3   Lena        4
4   Marc
5   Wei         5
6   Amara       6

The second half contributes nothing here because every order does have a customer — which is the point of writing it: it reports zero orphan orders as loudly as it reports Marc. Whenever you need FULL OUTER on SQLite, this is the shape to reach for.

Fan-out: when a join multiplies your rows

The last pitfall needs no NULLs at all. A one-to-many join gives you one row per item, so anything counted over the joined rows is counted once per item:

SELECT c.first_name, COUNT(*) AS rows_seen, COUNT(DISTINCT o.id) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id
ORDER BY c.id;

Expected output:

first_name  rows_seen  orders
----------  ---------  ------
Aarav       3          2
Priya       1          1
Lena        2          1
Wei         1          1
Amara       1          1

Aarav bought across two orders containing three items, and Lena’s single order contains two items — rows_seen counts the join’s output, orders counts reality. The same inflation corrupts SUM and AVG the moment a query mixes a one-to-many pair with an unrelated measure, so ask of every aggregate: “what exactly is one row here?” A join on a function, such as ON LOWER(c.email) = LOWER(o.email), hides a different trap — it works, but the expression defeats any index, and chapter 19 explains why that turns a millisecond into a scan.

Common mistakes

• Filtering a right-hand column in WHERE — NULL <> 'value' is NULL, so every padded row disappears; put the condition in ON or test with IS NULL.
• Using COUNT(*) on a left-joined query — it counts the null-padded row as 1; count the right table’s primary key instead.
• Assuming RIGHT and FULL OUTER exist everywhere — SQLite before 3.39 and MySQL before 8.0.18 reject them; flip the tables or use the UNION ALL form.
• Testing a nullable column for missingness — WHERE o.status IS NULL also catches a genuine NULL status; test the primary key, which is NULL only when nothing matched.
• Reading blanks as empty strings — those cells are NULL, so = '' misses them and COALESCE(o.status, 'none') is how you display them.
• Trusting an aggregate over joined rows — fan-out multiplies one order into several rows; count with DISTINCT or aggregate before you join.

Practice checklist

1. Run the left join above and confirm Marc’s row contains NULL, not an empty string, with WHERE o.id IS NULL.
2. Reproduce the NULL trap, then fix it by moving the condition into ON, and diff the two result sets row by row.
3. Find every customer whose signup came after 2025-12-01 using a left join plus an IS NULL anti-join against orders.
4. Write the RIGHT JOIN version of chapter 10’s customer list, and rewrite it as a plain left join for an older SQLite.
5. Run the fan-out query and explain Aarav’s 3 and 2 in one sentence each.

What's next?

Two-table joins now behave, and three-table joins already strain your eyes. Chapter 12 makes long queries readable with Multi-Table Joins and Aliases.