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