New versions: Dev (3.15) | Latest (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 | 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 NULL
yieldsTRUE
-
[ANY] IS NOT DISTINCT FROM NULL
yieldsFALSE
-
NULL IS DISTINCT FROM NULL
yieldsFALSE
-
NULL IS NOT DISTINCT FROM NULL
yieldsTRUE
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
Feedback
Do you have any feedback about this page? We'd love to hear it!