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 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
Feedback
Do you have any feedback about this page? We'd love to hear it!