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
NULL predicate (degree > 1)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL NULL predicate also works well for row value expressions, although it has some subtle, counter-intuitive features when it comes to inversing predicates with the NOT() operator! Here are some examples:
-- Row value expressions (A, B) IS NULL (A, B) IS NOT NULL -- Inverse of the above NOT((A, B) IS NULL) NOT((A, B) IS NOT NULL)
-- Equivalent factored-out predicates (A IS NULL) AND (B IS NULL) (A IS NOT NULL) AND (B IS NOT NULL) -- Inverse (A IS NOT NULL) OR (B IS NOT NULL) (A IS NULL) OR (B IS NULL)
The SQL standard contains a nice truth table for the above rules:
+-----------------------+-----------+---------------+---------------+-------------------+ | Expression | R IS NULL | R IS NOT NULL | NOT R IS NULL | NOT R IS NOT NULL | +-----------------------+-----------+---------------+---------------+-------------------+ | degree 1: null | true | false | false | true | | degree 1: not null | false | true | true | false | | degree > 1: all null | true | false | false | true | | degree > 1: some null | false | false | true | true | | degree > 1: none null | false | true | true | false | +-----------------------+-----------+---------------+---------------+-------------------+
In jOOQ, you would simply use the isNull() and isNotNull() methods on row value expressions. Again, as with the row value expression comparison predicate, the row value expression NULL predicate is emulated by jOOQ, if your database does not natively support it:
row(BOOK.ID, BOOK.TITLE).isNull(); row(BOOK.ID, BOOK.TITLE).isNotNull();
Dialect support
This example using jOOQ:
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNull()
Translates to the following dialect specific expressions:
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, COCKROACHDB, DB2, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, -- MARIADB, MEMSQL, MYSQL, ORACLE, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA ( AUTHOR.FIRST_NAME IS NULL AND AUTHOR.LAST_NAME IS NULL ) -- AURORA_POSTGRES, H2, POSTGRES, REDSHIFT, YUGABYTEDB (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) IS NULL
(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!