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

• The CASE expression
• The JOIN clause (or JOIN .. ON clause, to be precise) of a SELECT statement, UPDATE statement, DELETE statement
• The WHERE clause of a SELECT statement, UPDATE statement, DELETE statement
• The CONNECT BY clause of a SELECT statement
• The HAVING clause of a SELECT statement
• The MERGE statement's ON clause

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.

Table of contents

3.9.1.

Condition building

3.9.2.

TRUE and FALSE condition

3.9.3.

BOOLEAN columns

3.9.4.

AND, OR, NOT boolean operators

3.9.5.

Boolean operator precedence

3.9.6.

Comparison predicate

3.9.7.

Comparison predicate (degree > 1)

3.9.8.

Quantified comparison predicate

3.9.9.

BETWEEN predicate

3.9.10.

BETWEEN predicate (degree > 1)

3.9.11.

DISTINCT predicate

3.9.12.

DISTINCT predicate (degree > 1)

3.9.13.

DOCUMENT predicate (new)

3.9.14.

EXISTS predicate

3.9.15.

IN predicate

3.9.16.

IN predicate (degree > 1)

3.9.17.

JSON predicate

3.9.18.

JSON_EXISTS predicate (new)

3.9.19.

LIKE predicate

3.9.20.

Quantified LIKE predicate

3.9.21.

NULL predicate

3.9.22.

NULL predicate (degree > 1)

3.9.23.

OVERLAPS predicate

3.9.24.

SIMILAR TO predicate

3.9.25.

UNIQUE predicate

3.9.26.

XMLEXISTS predicate (new)

3.9.27.

Query By Example (QBE)