| The jOOQ User Manual. Multiple Pages : SQL building : Conditional expressions : DISTINCT predicate | previous : next |
New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
DISTINCT predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases support the DISTINCT predicate, which serves as a convenient, NULL-safe comparison predicate. With the DISTINCT predicate, the following truth table can be assumed:
-
[ANY] IS DISTINCT FROM NULLyieldsTRUE -
[ANY] IS NOT DISTINCT FROM NULLyieldsFALSE -
NULL IS DISTINCT FROM NULLyieldsFALSE -
NULL IS NOT DISTINCT FROM NULLyieldsTRUE
For instance, you can compare two fields for distinctness, ignoring the fact that any of the two could be NULL, which would lead to funny results. This is supported by jOOQ as such:
TITLE IS DISTINCT FROM SUB_TITLE TITLE IS NOT DISTINCT FROM SUB_TITLE
BOOK.TITLE.isDistinctFrom(BOOK.SUB_TITLE) BOOK.TITLE.isNotDistinctFrom(BOOK.SUB_TITLE)
If your database does not natively support the DISTINCT predicate, jOOQ emulates it with an equivalent CASE expression, modelling the above truth table:
-- A IS DISTINCT FROM B
CASE WHEN A IS NULL AND B IS NULL THEN FALSE
WHEN A IS NULL AND B IS NOT NULL THEN TRUE
WHEN A IS NOT NULL AND B IS NULL THEN TRUE
WHEN A = B THEN FALSE
ELSE TRUE
END
-- A IS NOT DISTINCT FROM B
CASE WHEN A IS NULL AND B IS NULL THEN TRUE
WHEN A IS NULL AND B IS NOT NULL THEN FALSE
WHEN A IS NOT NULL AND B IS NULL THEN FALSE
WHEN A = B THEN TRUE
ELSE FALSE
END
