Databases · Middle · Question 30 of 75

Why can `WHERE id NOT IN (SELECT user_id FROM bans)` return no rows at all?

✓Correct answer: If the subquery returns a NULL, NOT IN can never be true, so every row is filtered out; NOT EXISTS avoids this
2Because id must be indexed
3NOT IN doesn't work with subqueries

Explanation

x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the second part is unknown. This is a classic bug with nullable columns.

Further reading

Test yourself

Take the Databases · Middle quiz: questions and options are shuffled, and you get a review of your mistakes at the end.