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:

-- ACCESS, DERBY, MEMSQL
EXISTS (
  SELECT alias_1.v0, alias_1.v1
  FROM (
    SELECT
      AUTHOR.FIRST_NAME AS v0,
      AUTHOR.LAST_NAME AS v1
    FROM AUTHOR
  ) AS alias_1
  WHERE (
    'FIRST' = alias_1.v0
    AND 'LAST' = alias_1.v1
  )
)

-- ASE, EXASOL, FIREBIRD, HANA, INFORMIX, 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, COCKROACHDB, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, REDSHIFT, SQLITE, TERADATA, 
-- YUGABYTE
('FIRST', 'LAST') IN (
  SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  FROM AUTHOR
)

-- BIGQUERY
EXISTS (
  SELECT alias_1.v0, alias_1.v1
  FROM (
    SELECT
      AUTHOR.FIRST_NAME AS v0,
      AUTHOR.LAST_NAME AS v1
    FROM AUTHOR
  ) AS alias_1
  WHERE ('FIRST', 'LAST') = (alias_1.v0, alias_1.v1)
)

-- 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)
)

-- ORACLE
('FIRST', 'LAST') IN ((
  SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  FROM AUTHOR
))

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

The jOOQ User Manual : SQL building : Conditional expressions : IN predicate (degree > 1)

previous : next