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
AND, OR, NOT boolean operators
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In SQL, as in most other languages, conditional expressions can be connected using the AND and OR binary operators, as well as the NOT unary operator, to form new conditional expressions. In jOOQ, this is modelled as such:
(TITLE = 'Animal Farm' OR TITLE = '1984') AND NOT (AUTHOR.LAST_NAME = 'Orwell')
BOOK.TITLE.eq("Animal Farm").or(BOOK.TITLE.eq("1984"))
.andNot(AUTHOR.LAST_NAME.eq("Orwell"))
The above example shows that the number of parentheses in Java can quickly explode. Proper indentation may become crucial in making such code readable. In order to understand how jOOQ composes combined conditional expressions, let's assign component expressions first:
Condition a = BOOK.TITLE.eq("Animal Farm");
Condition b = BOOK.TITLE.eq("1984");
Condition c = AUTHOR.LAST_NAME.eq("Orwell");
Condition combined1 = a.or(b); // These OR-connected conditions form a new condition, wrapped in parentheses
Condition combined2 = combined1.andNot(c); // The left-hand side of the AND NOT () operator is already wrapped in parentheses
The Condition API
Here are all boolean operators on the org.jooq.Condition interface:
and(Condition) // Combine conditions with AND and(String) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side and(String, Object...) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side and(String, QueryPart...) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side andExists(Select<?>) // Combine conditions with AND. Convenience for adding an exists predicate to the rhs andNot(Condition) // Combine conditions with AND. Convenience for adding an inverted condition to the rhs andNotExists(Select<?>) // Combine conditions with AND. Convenience for adding an inverted exists predicate to the rhs or(Condition) // Combine conditions with OR or(String) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side or(String, Object...) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side or(String, QueryPart...) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side orExists(Select<?>) // Combine conditions with OR. Convenience for adding an exists predicate to the rhs orNot(Condition) // Combine conditions with OR. Convenience for adding an inverted condition to the rhs orNotExists(Select<?>) // Combine conditions with OR. Convenience for adding an inverted exists predicate to the rhs not() // Invert a condition (synonym for DSL.not(Condition)
References to this page
- SEEK clause implementation
- Condition building
- TRUE and FALSE condition
- Boolean operator precedence
- EXISTS predicate
- Operator precedence
- AND to NOT IN
- Merge AND predicates
- Merge IN predicates
- Merge NOT with comparison predicates
- Merge NOT with DISTINCT predicate
- Merge OR predicates
- Merge range predicates
- NOT AND
- NOT OR
- OR to IN
- Repeated NOT
- Kotlin BOOLEAN value expressions
Feedback
Do you have any feedback about this page? We'd love to hear it!