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
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 )
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
- IN-list Padding
- Projection type safety
- SEMI JOIN
- ANTI JOIN
- Quantified comparison predicate
- EXISTS predicate
- IN predicate (degree > 1)
- Synthetic SQL clauses
- IN condition subquery with LIMIT to derived table
- AND to NOT IN
- Merge IN predicates
- Normalise IN list with single element to comparison
- OR to IN
- Duplicate Statements
- Synthetic enums
- SQL: NOT IN predicate
- SQL: SELECT DISTINCT
Feedback
Do you have any feedback about this page? We'd love to hear it!