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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

# 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.