SQL Practice 21/26

Fix the bug: NOT IN quietly returns nothing

The last lesson's query was short a few rows. This one is worse: it returns nothing at all, and nothing is a perfectly plausible-looking answer. An empty result reads as "there are none" - so this query is quietly claiming that every single user has placed an order.

Look at the tables and you can see that is false. Ben and Sam are sitting right there with no orders against their names. The query is not asking the database a hard question. It is asking a question the database cannot answer true to, for any row, ever.

One thing changed since the last lesson: orders now has a sixth row - a gift card someone bought without logging in, so its user_id is NULL. Nothing else moved. That single row is what turned a working query into a query that returns nothing.

Your task: return the name of every user who has never placed an order. The answer is Ben and Sam.

You'll practice:

Reading an empty result as a claim, and checking whether the claim is true
Spotting that one NULL in a subquery can silently empty out NOT IN

-- This should list users who have never ordered. It returns nothing at all - fix it.
SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);