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

Comparison predicate (degree > 1)

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

All variants of the comparison predicate that we've seen in the previous chapter also work for row value expressions. If your database does not support row value expression comparison predicates, jOOQ emulates them the way they are defined in the SQL standard:

-- Row value expressions (equal)
(A, B)    =  (X, Y)
(A, B, C) =  (X, Y, Z)
-- greater than
(A, B)    >  (X, Y)

(A, B, C) >  (X, Y, Z)


-- greater or equal
(A, B)    >= (X, Y)


(A, B, C) >= (X, Y, Z)



-- Inverse comparisons

(A, B)    <> (X, Y)
(A, B)    <  (X, Y)
(A, B)    <= (X, Y)
-- Equivalent factored-out predicates (equal)
(A = X) AND (B = Y)
(A = X) AND (B = Y) AND (C = Z)
-- greater than
(A > X)
  OR ((A = X) AND (B > Y))
(A > X)
  OR ((A = X) AND (B > Y))
  OR ((A = X) AND (B = Y) AND (C > Z))
-- greater or equal
(A > X)
  OR ((A = X) AND (B > Y))
  OR ((A = X) AND (B = Y))
(A > X)
  OR ((A = X) AND (B > Y))
  OR ((A = X) AND (B = Y) AND (C > Z))
  OR ((A = X) AND (B = Y) AND (C = Z))
-- For simplicity, these predicates are shown in terms
-- of their negated counter parts
NOT((A, B) =  (X, Y))
NOT((A, B) >= (X, Y))
NOT((A, B) >  (X, Y))

jOOQ supports all of the above row value expression comparison predicates, both with column expression lists and scalar subselects at the right-hand side:

-- With regular column expressions
(BOOK.AUTHOR_ID, BOOK.TITLE) = (1, 'Animal Farm')

-- With scalar subselects
(BOOK.AUTHOR_ID, BOOK.TITLE) = (
  SELECT PERSON.ID, 'Animal Farm'
  FROM PERSON
  WHERE PERSON.ID = 1
)
// Column expressions
row(BOOK.AUTHOR_ID, BOOK.TITLE).eq(1, "Animal Farm");

// Subselects
row(BOOK.AUTHOR_ID, BOOK.TITLE).eq(
  select(PERSON.ID, val("Animal Farm"))
  .from(PERSON)
  .where(PERSON.ID.eq(1))
);

Dialect support

This example using jOOQ:

row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).eq("John", "Doe")

Translates to the following dialect specific expressions:

-- ACCESS, ASE, DERBY, EXASOL, FIREBIRD, HANA, MEMSQL, SQLDATAWAREHOUSE, SQLSERVER, SYBASE
(
  AUTHOR.FIRST_NAME = 'John'
  AND AUTHOR.LAST_NAME = 'Doe'
)

-- AURORA_MYSQL, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DB2, DUCKDB, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, REDSHIFT, 
-- SNOWFLAKE, SQLITE, TRINO, VERTICA, YUGABYTEDB
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) = ('John', 'Doe')

-- INFORMIX
ROW (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) = ROW ('John', 'Doe')

-- ORACLE
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) = (('John', 'Doe'))

-- TERADATA
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) = (
  SELECT 'John', 'Doe'
  FROM (
    SELECT 1 AS "dual"
  ) AS "dual"
)

(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