Share jOOQ on Facebook
Share jOOQ on Twitter

All versions: 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.10 | Unsupported versions: 3.2 | 3.1 | 3.0 | 2.6 | 2.5

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

... or better, if the INTERSECT set operation is supported:

-- [A] IS DISTINCT FROM [B]
NOT EXISTS(SELECT A INTERSECT SELECT B)
-- [A] IS NOT DISTINCT FROM [B]
EXISTS(SELECT a INTERSECT SELECT b)
The jOOQ Logo