The jOOQ User Manual Reference Don't do this SQL: NOT IN predicate

previous : next

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) is UNKNOWN, the result is either TRUE or UNKNOWN, the latter behaving like FALSE in a query. We're fine.
• If one value of a conjunction (AND) is UNKNOWN, the result is either FALSE or UNKNOWN, so the predicate always behaves as if it were FALSE. 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.

The jOOQ User Manual Reference Don't do this SQL: NOT IN predicate

previous : next