Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

Conditional expressions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Conditions or conditional expressions are widely used in SQL and in the jOOQ API. They can be used in

Boolean types in SQL

Before SQL:1999, boolean types did not really exist in SQL. They were modelled by 0 and 1 numeric/char values. With SQL:1999, true booleans were introduced and are now supported by most databases. In short, these are possible boolean values:

  • 1 or TRUE
  • 0 or FALSE
  • NULL or UNKNOWN

It is important to know that SQL differs from many other languages in the way it interprets the NULL boolean value. Most importantly, the following facts are to be remembered:

  • [ANY] = NULL yields NULL (not FALSE)
  • [ANY] != NULL yields NULL (not TRUE)
  • NULL = NULL yields NULL (not TRUE)
  • NULL != NULL yields NULL (not FALSE)

For simplified NULL handling, please refer to the section about the DISTINCT predicate.

Note that jOOQ does not model these values as actual column expression compatible.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo