The jOOQ User Manual. Multiple Pages : SQL building : Conditional expressions : DISTINCT predicate | previous : next |
All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6
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