|The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements : The SELECT statement : UNION, INTERSECTION and EXCEPT||previous : next|
UNION, INTERSECTION and EXCEPT
Available in ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
SQL allows to perform set operations as understood in standard set theory on result sets. These operations include unions, intersections, subtractions. For two subselects to be combinable by such a set operator, each subselect must return a table expression of the same arity and type.
These operators combine two results into one. While UNION removes all duplicate records resulting from this combination, UNION ALL leaves subselect results as they are. Typically, you should prefer UNION ALL over UNION, if you don't really need to remove duplicates. The following example shows how to use such a UNION operation in jOOQ.
SELECT * FROM BOOK WHERE ID = 3 UNION ALL SELECT * FROM BOOK WHERE ID = 5
create.selectFrom(BOOK).where(BOOK.ID.eq(3)) .unionAll( create.selectFrom(BOOK).where(BOOK.ID.eq(5)));
INTERSECT is the operation that produces only those s that are returned by both subselects. EXCEPT is the operation that returns only those s that are returned exclusively in the first subselect. Both operators will remove duplicates from their results. The SQL standard allows to specify the ALL keyword for both of these operators as well, but this is hardly supported in any database. jOOQ does not support INTERSECT ALL, EXEPT ALL operations either.
As previously mentioned in the manual's section about the ORDER BY clause, jOOQ has slightly changed the semantics of these set operators. While in SQL, a subselect may not contain any ORDER BY clause or LIMIT clause (unless you wrap the subselect into a nested SELECT), jOOQ allows you to do so. In order to select both the youngest and the oldest author from the database, you can issue the following statement with jOOQ (rendered to the MySQL dialect):
(SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH ASC LIMIT 1) UNION (SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH DESC LIMIT 1)
create.selectFrom(AUTHOR) .orderBy(AUTHOR.DATE_OF_BIRTH.asc()).limit(1) .union( create.selectFrom(AUTHOR) .orderBy(AUTHOR.DATE_OF_BIRTH.desc()).limit(1));