The NULL traps

Break it on purposeNeeds Python with DuckDB

Problem

SQL's three-valued logic and join semantics produce answers that look plausible and are wrong. NOT IN compares against every value in the subquery, and one NULL makes every comparison unknown, so the query returns nothing; NOT EXISTS is not affected. A filter on the right table of a LEFT JOIN placed in WHERE discards the unmatched rows, turning it into an inner join; placed in ON, it keeps them. And a join to a table with several rows per key multiplies every measure summed afterwards.

Create blocked(customer_id) with a few ids. Run NOT IN and NOT EXISTS and compare row counts. Insert one NULL into blocked and run both again. Then run the LEFT JOIN ... WHERE c.tier = 3 and the LEFT JOIN ... ON ... AND c.tier = 3 forms and count rows. Finally join orders to a table with two rows per order and compare SUM(amount) before and after.

The question. How does each trap change the result, without any error?

Predict

LEFT JOIN with the filter c.tier = 3 in WHERE instead of ON: how many of the 5,000 orders remain?

All 5,000Only the tier-3 matchesNone

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

the-null-traps.py41 linesDownload

View the code

the-null-traps.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["duckdb"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""The NULL traps.

DuckDB, small tables. NOT IN against NOT EXISTS before and after one NULL is
inserted into the subquery's table; a LEFT JOIN with the filter in WHERE
against the filter in ON; and SUM(amount) before and after a join to a table
with two rows per order.
"""
import argparse, json
import duckdb

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

c = duckdb.connect()
c.execute("CREATE TABLE customers AS SELECT i AS customer_id, i % 5 AS tier FROM range(1, 1001) t(i)")
c.execute("CREATE TABLE orders AS SELECT i AS order_id, 1 + i % 1000 AS customer_id, 10 AS amount FROM range(0, 5000) t(i)")
c.execute("CREATE TABLE blocked AS SELECT * FROM (VALUES (1), (2), (3)) t(customer_id)")
c.execute("CREATE TABLE shipments AS SELECT order_id, s FROM orders, range(2) t(s)")          # two rows per order
q = lambda s: c.execute(s).fetchone()[0]
NOT_IN = "SELECT count(*) FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM blocked)"
NOT_EX = "SELECT count(*) FROM customers c WHERE NOT EXISTS (SELECT 1 FROM blocked b WHERE b.customer_id = c.customer_id)"
rows = [{"trap": "NOT IN", "case": "blocked has 3 ids", "result": q(NOT_IN)}, {"trap": "NOT EXISTS", "case": "blocked has 3 ids", "result": q(NOT_EX)}]
c.execute("INSERT INTO blocked VALUES (NULL)")
rows += [{"trap": "NOT IN", "case": "plus one NULL", "result": q(NOT_IN)}, {"trap": "NOT EXISTS", "case": "plus one NULL", "result": q(NOT_EX)}]
rows += [{"trap": "LEFT JOIN, filter in WHERE", "case": "c.tier = 3", "result": q("SELECT count(*) FROM orders o LEFT JOIN customers c USING (customer_id) WHERE c.tier = 3")},
         {"trap": "LEFT JOIN, filter in ON", "case": "AND c.tier = 3", "result": q("SELECT count(*) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id AND c.tier = 3")}]
rows += [{"trap": "SUM(amount)", "case": "orders alone", "result": q("SELECT sum(amount) FROM orders")},
         {"trap": "SUM(amount)", "case": "after joining two shipments per order", "result": q("SELECT sum(amount) FROM orders JOIN shipments USING (order_id)")}]
for r in rows: print(f"{r['trap']:<28} {r['case']:<40} {r['result']:>8,}")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"traps": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run the-null-traps.py
uv run the-null-traps.py --help     # every knob
```

Our run

Three traps

Three traps
Query	Case	Result
NOT IN	blocked has 3 ids	997
NOT EXISTS	blocked has 3 ids	997
NOT IN	plus one NULL	0
NOT EXISTS	plus one NULL	997
LEFT JOIN, filter in WHERE	c.tier = 3	1K
LEFT JOIN, filter in ON	AND c.tier = 3	5K
SUM(amount)	orders alone	50K
SUM(amount)	after joining two shipments per order	100K

Reference runApple M2 · 8 cores · 8 GB

Output · uv run the-null-traps.py

NOT IN                       blocked has 3 ids                             997
NOT EXISTS                   blocked has 3 ids                             997
NOT IN                       plus one NULL                                   0
NOT EXISTS                   plus one NULL                                 997
LEFT JOIN, filter in WHERE   c.tier = 3                                  1,000
LEFT JOIN, filter in ON      AND c.tier = 3                              5,000
SUM(amount)                  orders alone                               50,000
SUM(amount)                  after joining two shipments per order     100,000

macOS 26.6.2 · Python 3.13.3 · duckdb 1.5.6 · run 2026-10-20

What we found

With three blocked ids, both forms returned 997 customers. After one NULL, NOT IN returned 0 and NOT EXISTS still 997. The LEFT JOIN with the filter in WHERE kept 1K rows, only tier-3 matches; in ON it kept all 5K orders. The SUM went from 50K to 100K after the join.

Read it

Each result follows from the rules: an unknown comparison filters a row out, WHERE runs after the outer join, and a join repeats rows. None raises an error, so the defences are habits: anti-joins as NOT EXISTS, filters on the optional side in ON, and aggregates computed at the grain they belong to before joining.