Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
SQL: NOT IN predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The NOT IN
predicate seems to be just the inverse of the useful IN predicate, but in SQL, this isn't entirely true, thanks to SQL's three valued logic.
Look at the following transformations of equivalent predicates:
-- IN predicate is equivalent to: A IN (B, C) A = ANY (B, C) A = B OR A = C -- NOT IN predicate is equivalent to: A NOT IN (B, C) A <> ANY (B, C) A <> B AND A <> C
Now, imagine if one of the values is NULL
, then, informally:
-- IN predicate is equivalent to: A IN (B, NULL) A = ANY (B, NULL) A = B OR A = NULL A = B OR NULL A = B -- NOT IN predicate is equivalent to: A NOT IN (B, NULL) A <> ANY (B, NULL) A <> B AND A <> NULL A <> B AND NULL NULL
Think of NULL
as UNKNOWN
:
- If one value of a disjunction (
OR
) isUNKNOWN
, the result is eitherTRUE
orUNKNOWN
, the latter behaving likeFALSE
in a query. We're fine. - If one value of a conjunction (
AND
) isUNKNOWN
, the result is eitherFALSE
orUNKNOWN
, so the predicate always behaves as if it wereFALSE
. This is never what we want!
To make things worse, if you're using NOT IN (subquery)
, this problem can happen occasionally only, when the subquery returns a single NULL
value. It's logical, but never useful. So better just use the NOT EXISTS predicate instead.
See also this blog post, which talks about compatibility across dialects.
Feedback
Do you have any feedback about this page? We'd love to hear it!