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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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
- 3.12.1.
- Condition building
- 3.12.2.
- TRUE and FALSE condition
- 3.12.3.
- BOOLEAN columns
- 3.12.4.
- AND, OR, NOT boolean operators
- 3.12.5.
- Boolean operator precedence
- 3.12.6.
- XOR boolean operator
- 3.12.7.
- Comparison predicate
- 3.12.8.
- Comparison predicate (degree > 1)
- 3.12.9.
- Quantified comparison predicate
- 3.12.10.
- BETWEEN predicate
- 3.12.11.
- BETWEEN predicate (degree > 1)
- 3.12.12.
- DISTINCT predicate
- 3.12.13.
- DISTINCT predicate (degree > 1)
- 3.12.14.
- DOCUMENT predicate
- 3.12.15.
- EXISTS predicate
- 3.12.16.
- IN predicate
- 3.12.17.
- IN predicate (degree > 1)
- 3.12.18.
- JSON predicate
- 3.12.19.
- JSON_EXISTS predicate
- 3.12.20.
- LIKE predicate
- 3.12.21.
- Quantified LIKE predicate
- 3.12.22.
- NULL predicate
- 3.12.23.
- NULL predicate (degree > 1)
- 3.12.24.
- OVERLAPS predicate
- 3.12.25.
- SIMILAR TO predicate
- 3.12.26.
- Spatial predicates
- 3.12.26.1.
- ST_Contains
- 3.12.26.2.
- ST_Crosses
- 3.12.26.3.
- ST_Disjoint
- 3.12.26.4.
- ST_Equals
- 3.12.26.5.
- ST_Intersects
- 3.12.26.6.
- ST_IsClosed
- 3.12.26.7.
- ST_IsEmpty
- 3.12.26.8.
- ST_IsRing (new)
- 3.12.26.9.
- ST_IsSimple (new)
- 3.12.26.10.
- ST_IsValid (new)
- 3.12.26.11.
- ST_Overlaps
- 3.12.26.12.
- ST_Touches
- 3.12.26.13.
- ST_Within
- 3.12.27.
- UNIQUE predicate
- 3.12.28.
- XMLEXISTS predicate
- 3.12.29.
- Query By Example (QBE)
previous : next |
References to this page
- The query DSL type
- Mutability (historic)
- JOIN operator
- WHERE clause
- UPDATE .. WHERE
- DELETE .. WHERE
- Bitwise functions
- JSON functions
- XML functions
- Tuples or row value expressions
- Condition building
- TRUE and FALSE condition
- AND, OR, NOT boolean operators
- EXISTS predicate
- IN predicate
- NULL predicate
- Dynamic SQL
- Optional column expressions
- Optional conditional expressions
- Plain SQL
- SQL Parser Listener
- Trivial predicates
- Policies
- Kotlin BOOLEAN value expressions
- Overriding where()
- BOOLEAN data type
Feedback
Do you have any feedback about this page? We'd love to hear it!