The jOOQ User Manual : SQL building : Conditional expressions : Comparison predicate (degree > 1) | previous : next |

New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | **3.5** | 3.4 | 3.3

# 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, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, REDSHIFT, -- SNOWFLAKE, SQLITE, VERTICA, YUGABYTE (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.16, see #10141), or translate your own on our website

## Feedback

Do you have any feedback about this page? We'd love to hear it!