| 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 yields TRUE
- [ANY] IS NOT DISTINCT FROM NULL yields FALSE
- NULL IS DISTINCT FROM NULL yields FALSE
- NULL IS NOT DISTINCT FROM NULL yields TRUE
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
