Catching NULL - which comparison actually works?

Question

Accepted Answer

NULL = NULL evaluates to UNKNOWN in SQL, not TRUE. So CASE WHEN discount = NULL never matches. The simple CASE form (CASE discount WHEN NULL ...) also uses equality internally and will never match NULL. The only correct test for NULL is IS NULL. CASE WHEN discount IS NULL correctly returns 0 for NULL rows and the real discount otherwise. COALESCE(discount, 0) is the shorter equivalent - both compile to the same plan in most databases.