Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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
orTRUE
-
0
orFALSE
-
NULL
orUNKNOWN
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
yieldsNULL
(notFALSE
) -
[ANY] != NULL
yieldsNULL
(notTRUE
) -
NULL = NULL
yieldsNULL
(notTRUE
) -
NULL != NULL
yieldsNULL
(notFALSE
)
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
- 4.8.1.
- Condition building
- 4.8.2.
- TRUE and FALSE condition
- 4.8.3.
- BOOLEAN columns
- 4.8.4.
- AND, OR, NOT boolean operators
- 4.8.5.
- Comparison predicate
- 4.8.6.
- Boolean operator precedence
- 4.8.7.
- Comparison predicate (degree > 1)
- 4.8.8.
- Quantified comparison predicate
- 4.8.9.
- NULL predicate
- 4.8.10.
- NULL predicate (degree > 1)
- 4.8.11.
- DISTINCT predicate
- 4.8.12.
- BETWEEN predicate
- 4.8.13.
- BETWEEN predicate (degree > 1)
- 4.8.14.
- LIKE predicate
- 4.8.15.
- IN predicate
- 4.8.16.
- IN predicate (degree > 1)
- 4.8.17.
- EXISTS predicate
- 4.8.18.
- OVERLAPS predicate
- 4.8.19.
- Query By Example (QBE)
previous : next |
References to this page
- The query DSL type
- jOOQ's DSL and model API
- JOIN operator
- WHERE clause
- UPDATE .. WHERE
- DELETE .. WHERE
- Bitwise functions
- Tuples or row value expressions
- Condition building
- TRUE and FALSE condition
- AND, OR, NOT boolean operators
- NULL predicate
- IN predicate
- EXISTS predicate
- Dynamic SQL
- Plain SQL
- BOOLEAN data type
Feedback
Do you have any feedback about this page? We'd love to hear it!