Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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.21. Translate your own SQL on our website
References to this page
- Settings: IN-list padding
- The type safety of the projected records in SELECT
- The SEMI JOIN operator
- The ANTI JOIN operator
- The quantified comparison predicate
- The EXISTS predicate
- The IN predicate for degrees higher than 1
- Synthetic SQL clauses
- IN condition subquery with LIMIT to derived table
- Pattern based transformations: AND to NOT IN
- Pattern based transformations: Merge IN predicates
- Pattern based transformations: Normalise single element IN lists
- Pattern based transformations: OR to IN
- Duplicate Statements
- Synthetic enums in the code generator
- Don't do this in SQL: NOT IN predicate
- Don't do this in SQL: SELECT DISTINCT
Feedback
Do you have any feedback about this page? We'd love to hear it!