|The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : UNION, INTERSECTION and EXCEPT||previous : next|
UNION, INTERSECTION and EXCEPT
Applies to ✅ 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 degree 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))) .fetch();
INTERSECT is the operation that produces only those values that are returned by both subselects.
MINUS in Oracle) is the operation that returns only those values that are returned exclusively in the first subselect. Both operators will remove duplicates from their results.
Just like with
UNION ALL, these operators have an optional
ALL keyword that allows for keeping duplicate rows after intersection or subtraction, which is supported in jOOQ 3.7+.
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 derived table), 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) ORDER BY 1
create.selectFrom(AUTHOR) .orderBy(AUTHOR.DATE_OF_BIRTH.asc()).limit(1) .union( selectFrom(AUTHOR) .orderBy(AUTHOR.DATE_OF_BIRTH.desc()).limit(1)) .orderBy(1) .fetch();
In case your database doesn't support ordered
UNION subselects, the subselects are nested in derived tables:
SELECT * FROM ( SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH ASC LIMIT 1 ) UNION SELECT * FROM ( SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH DESC LIMIT 1 ) ORDER BY 1
Two subselects that are combined by a set operator are required to be of the same degree and, in most databases, also of the same type. jOOQ 3.0's introduction of Typesafe Record[N] types helps compile-checking these constraints:
// Some sample SELECT statements Select<Record2<Integer, String>> s1 = select(BOOK.ID, BOOK.TITLE).from(BOOK); Select<Record1<Integer>> s2 = selectOne(); Select<Record2<Integer, Integer>> s3 = select(one(), zero()); Select<Record2<Integer, String>> s4 = select(one(), inline("abc")); // Let's try to combine them: s1.union(s2); // Doesn't compile because of a degree mismatch. Expected: Record2<...>, got: Record1<...> s1.union(s3); // Doesn't compile because of a type mismatch. Expected: <Integer, String>, got: <Integer, Integer> s1.union(s4); // OK. The two Record[N] types match