Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

IN predicate

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In SQL, apart from comparing a value against several values, the IN predicate can be used to create semi-joins or anti-joins. jOOQ knows the following methods on the org.jooq.Field interface, to construct such IN predicates:

in(Collection<?>)                   // Construct an IN predicate from a collection of bind values
in(T...)                            // Construct an IN predicate from bind values
in(Field<?>...)                     // Construct an IN predicate from column expressions
in(Select<? extends Record1<T>>)    // Construct an IN predicate from a subselect
notIn(Collection<?>)                // Construct a NOT IN predicate from a collection of bind values
notIn(T...)                         // Construct a NOT IN predicate from bind values
notIn(Field<?>...)                  // Construct a NOT IN predicate from column expressions
notIn(Select<? extends Record1<T>>) // Construct a NOT IN predicate from a subselect

A sample IN predicate might look like this:

TITLE     IN ('Animal Farm', '1984')
TITLE NOT IN ('Animal Farm', '1984')
BOOK.TITLE.in("Animal Farm", "1984")
BOOK.TITLE.notIn("Animal Farm", "1984")

NOT IN and NULL values

Beware that you should probably not have any NULL values in the right hand side of a NOT IN predicate, as the whole expression would evaluate to NULL, which is rarely desired. This can be shown informally using the following reasoning:

-- The following conditional expressions are formally or informally equivalent
A NOT IN (B, C)
A != ANY(B, C)
A != B AND A != C

-- Substitute C for NULL, you'll get
A NOT IN (B, NULL)   -- Substitute C for NULL
A != B AND A != NULL -- From the above rules
A != B AND NULL      -- [ANY] != NULL yields NULL
NULL                 -- [ANY] AND NULL yields NULL

A good way to prevent this from happening is to use the EXISTS predicate for anti-joins, which is NULL-value insensitive. See the manual's section about conditional expressions to see a boolean truth table.

Dialect support

This example using jOOQ:

val("TITLE").in(select(BOOK.TITLE).from(BOOK))

Translates to the following dialect specific expressions:

-- All dialects
'TITLE' IN (
  SELECT BOOK.TITLE
  FROM BOOK
)

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo