All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.12 | Unsupported versions: 3.2 | 3.1 | 3.0

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();
The jOOQ Logo