What does SELECT product FROM filters WHERE category NOT IN ('A', 'B', NULL) return? SQL NULL, CASE and Set Operations

Answer, D. (no rows)

A. P1 P2 P3

This assumes the NULL in the list is simply ignored, leaving NOT IN to behave as if the list only had A and B, but SQL does not drop NULL members from a list before evaluating it.

B. P3

This picks out a single row as if only D somehow escaped the NULL trap, but the NULL in the list affects every row identically, there is no partial escape.

C. P2 P3

This is the answer a straightforward reading of NOT IN would suggest, categories C and D are not A or B, but a NULL sitting inside the list turns every row unknown regardless of its own category.

D. (no rows)

Correct. Because the list itself contains a NULL, NOT IN can never be confirmed true for any row, so the query returns nothing even though P2 and P3 categories look like an obvious match at first glance.

NOT IN (A, B, NULL) unfolds into value <> A AND value <> B AND value <> NULL, and the last comparison is always unknown. Unknown combined with AND drags the whole expression to unknown or false depending on the other terms, but never to true, so a NULL anywhere inside a NOT IN list quietly empties the result for every row.