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 (degree > 1)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL IN predicate also works well for row value expressions. Much like the IN predicate for degree 1, it is defined in terms of a quantified comparison predicate. The two expressions are equivalent:
R IN [IN predicate value]
R = ANY [IN predicate value]
jOOQ supports the IN predicate with row value expressions. An example is given here:
-- Using an IN list (BOOK.ID, BOOK.TITLE) IN ((1, 'A'), (2, 'B')) -- Using a subselect (BOOK.ID, BOOK.TITLE) IN ( SELECT T.ID, T.TITLE FROM T )
// Using an IN list row(BOOK.ID, BOOK.TITLE).in(row(1, "A"), row(2, "B")); // Using a subselect row(BOOK.ID, BOOK.TITLE).in( select(T.ID, T.TITLE) .from(T) );
In both cases, i.e. when using an IN list or when using a subselect, the type of the predicate is checked. Both sides of the predicate must be of equal degree and row type.
Emulation of the IN predicate where row value expressions aren't well supported is currently only available for IN predicates that do not take a subselect as an IN predicate value.
Dialect support
This example using jOOQ:
row("FIRST", "LAST").in(select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).from(AUTHOR))
Translates to the following dialect specific expressions:
ASE, Access, Derby, DuckDB, Exasol, Firebird, Hana, Informix, MemSQL, SQLDataWarehouse, SQLServer, Sybase
EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
AUTHOR.FIRST_NAME v0,
AUTHOR.LAST_NAME v1
FROM AUTHOR
) alias_1
WHERE (
'FIRST' = alias_1.v0
AND 'LAST' = alias_1.v1
)
)
Aurora MySQL, Aurora Postgres, ClickHouse, CockroachDB, H2, HSQLDB, MariaDB, MySQL, Postgres, Redshift, SQLite, Teradata, Trino, YugabyteDB
('FIRST', 'LAST') IN (
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
)
BigQuery, DB2, Snowflake, Vertica
EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
AUTHOR.FIRST_NAME v0,
AUTHOR.LAST_NAME v1
FROM AUTHOR
) alias_1
WHERE ('FIRST', 'LAST') = (alias_1.v0, alias_1.v1)
)
Databricks
EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
AUTHOR.FIRST_NAME v0,
AUTHOR.LAST_NAME v1
FROM AUTHOR
) alias_1
WHERE ('FIRST', 'LAST') = (
coalesce(alias_1.v0),
coalesce(alias_1.v1)
)
)
Oracle
('FIRST', 'LAST') IN ((
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
))
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!