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
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!