When NOT IN silently drops all rows

Question

Accepted Answer

NOT IN breaks when the subquery returns any NULL because id != NULL is UNKNOWN, and AND UNKNOWN collapses the entire WHERE to UNKNOWN. The row is silently dropped. NOT EXISTS is a correlated subquery that checks whether a matching row exists - it does not compare values directly, so NULLs in the subquery do not affect it. As a rule: if you cannot guarantee the subquery column is NOT NULL, use NOT EXISTS. The two are logically equivalent when NULLs are absent.