Because of that one NULL. If the subquery returns even a single NULL, NOT IN can never be true, so the query returns nothing. No error, no warning, just an empty result that looks believable.
Why it happens
NOT IN is shorthand for a chain of "not equal" checks joined with AND:
-- id NOT IN (101, 102, NULL) really means: id <> 101 AND id <> 102 AND id <> NULL
Any comparison with NULL gives UNKNOWN, not TRUE or FALSE. TRUE AND UNKNOWN is still UNKNOWN. WHERE only keeps rows where the condition is TRUE, so every row is thrown out.
Small example
customers has ids 1, 2 and 3. orders.customer_id has 1 and NULL (a guest checkout).
SELECT c.id FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders); -- 0 rows, even though customers 2 and 3 never ordered
The fix
Use NOT EXISTS. It checks each customer for a matching order, and a NULL customer_id simply never matches anyone:
SELECT c.id FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id ); -- returns 2 and 3
A LEFT JOIN with WHERE o.customer_id IS NULL also works. Adding WHERE customer_id IS NOT NULL inside the NOT IN subquery fixes it too, but people forget it the next time they copy the query. NOT EXISTS is the safer habit.
What the interviewer is checking
This is the classic trap inside "find customers with no orders". They want to hear "three-valued logic" and see you pick NOT EXISTS without being pushed. A likely follow-up: "Does IN have the same problem?" Not really. 1 IN (1, NULL) is still TRUE. The NULL only hurts the negative form.