SQLHard

The query that returns nothing, and is not wrong

A stock report lists products that have never been ordered. It worked for a year and now returns zero rows, every time, on a catalogue with obvious dead stock in it.

order_items.product_id is nullable — a manual adjustment line has no product. Return the products that have never been ordered.

Example

inputproducts(id, name), order_items(id, order_id, product_id NULL)outputid, name

Ordered by id. A line with a NULL product_id refers to no product and must not conceal anything.

Constraints

The result must not change when a NULL product_id is inserted.
One query.

Hints

Hint 1

Work out what `5 NOT IN (1, 2, NULL)` evaluates to. It is not TRUE, and it is not FALSE.

Hint 2

SQL's NULL means unknown, so `5 <> NULL` is unknown, and NOT IN is a chain of those tests ANDed together — one unknown makes the whole thing unknown, and WHERE keeps only TRUE.

Hint 3

NOT EXISTS asks a different question: does a matching row exist? A row with a NULL product_id does not match anything.

Stuck? The lesson behind this problem: 🐘 Subqueries and CTEs

sql

Tab indents · Escape first to tab out

Runs on SQLite in your browser; written for MySQL. Joins, CTEs and window functions behave the same; anything about the planner does not.

Test cases

These are the specification. Run tests checks your answer against them.

Case	Input	Expected
one product never ordered	`2 products, 1 ordered`	`the other one`
a NULL line hides nothing	`the same, plus a NULL product_id`	`the same answer`
only NULL lines exist	`2 products, 2 NULL lines`	`both products`