Why it happens
x NOT IN (a, b, ...) is equivalent to x <> a AND x <> b AND ....
Because returns contains (2, NULL), the predicate for Desk (id = 2) expands in three-valued logic to:
2 <> 1 AND 2 <> NULL
-- TRUE AND UNKNOWN --> UNKNOWN
In a WHERE clause, UNKNOWN is filtered out just like FALSE, so no row can ever pass NOT IN when the subquery returns even a single NULL.
---
Fix 1: Use NOT EXISTS (Recommended)
NOT EXISTS tests whether a matching row is found, sidestepping NULL comparisons on unmatched rows:
SELECT p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM returns r
WHERE r.product_id = p.id
)
ORDER BY p.name;
Fix 2: Exclude NULLs in the NOT IN subquery
SELECT name
FROM products
WHERE id NOT IN (
SELECT product_id
FROM returns
WHERE product_id IS NOT NULL
)
ORDER BY name;
Both return Chair and Desk. (NOT EXISTS is generally preferred because it doesn't silently break if a nullable column is added later.)
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: Chair and Desk (never returned).
SQL block 1 (2 <> 1 AND 2 <> NULL -- TRUE AND UNKNOWN --> UNKNOWN): not run (an illustration, not a query).
SQL block 2 (SELECT p.name FROM products p): ✅ matches the expected result
| name |
|---|
| Chair |
| Desk |
SQL block 3 (SELECT name FROM products): ✅ matches the expected result
| name |
|---|
| Chair |
| Desk |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.