UNION and other set operations

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# jOOQ's set operation API

The org.jooq.Select API directly supports the UNION syntax for all types of Select as discussed in the manual's section about Queries and Query subtypes. It consists of these methods:

public interface Select<R extends Record> {
    Select<R> union(Select<R> select);
    Select<R> unionAll(Select<R> select);
    Select<R> except(Select<R> select);
    Select<R> intersect(Select<R> select);
}

Hence, this is how you can write a simple UNION with jOOQ:

SELECT TITLE
  FROM T_BOOK
 WHERE PUBLISHED_IN > 1945
UNION
SELECT TITLE
  FROM T_BOOK
 WHERE AUTHOR_ID = 1

create.select(TBook.TITLE)
      .from(T_BOOK)
      .where(T_BOOK.PUBLISHED_IN.greaterThan(1945))
      .union(
create.select(T_BOOK.TITLE)
      .from(T_BOOK)
      .where(T_BOOK.AUTHOR_ID.equal(1)));

# Nested UNIONs

In some SQL dialects, you can arbitrarily nest UNIONs to several levels. Be aware, though, that SQLite, Derby and MySQL have serious syntax limitations. jOOQ tries to render correct UNION SQL statements, but unfortunately, you can create situations that will cause syntax errors in the aforementioned dialects.

An example of advanced UNION usage is the following statement in jOOQ:

// Create a UNION of several types of books
Select<?> union =
    create.select(T_BOOK.TITLE, T_BOOK.AUTHOR_ID).from(T_BOOK).where(T_BOOK.PUBLISHED_IN.greaterThan(1945)).union(
    create.select(T_BOOK.TITLE, T_BOOK.AUTHOR_ID).from(T_BOOK).where(T_BOOK.AUTHOR_ID.equal(1)));

// Now, re-use the above UNION and order it by author
create.select(union.getField(T_BOOK.TITLE))
      .from(union)
      .orderBy(union.getField(T_BOOK.AUTHOR_ID).descending());

This example does not seem surprising, when you have read the previous chapters about nested SELECT statements. But when you check out the rendered SQL:

-- alias_38173 is an example of a generated alias,
-- generated by jOOQ for union queries
SELECT alias_38173.TITLE FROM (
  SELECT T_BOOK.TITLE, T_BOOK.AUTHOR_ID FROM T_BOOK WHERE T_BOOK.PUBLISHED_IN > 1945
  UNION
  SELECT T_BOOK.TITLE, T_BOOK.AUTHOR_ID FROM T_BOOK WHERE T_BOOK.AUTHOR_ID = 1
) alias_38173
ORDER BY alias_38173.AUTHOR_ID DESC

You can see that jOOQ takes care of many syntax pitfalls, when you're not used to the various dialects' unique requirements. The above automatic aliasing was added in order to be compliant with MySQL's requirements about aliasing nested selects.

# Several UNIONs

It is no problem either for you to create SQL statements with several unions. Just write:

Select<?> part1;
Select<?> part2;
Select<?> part3;
Select<?> part4;

// [...]

part1.union(part2).union(part3).union(part4);

# UNION and the ORDER BY clause

Strictly speaking, in SQL, you cannot order a subselect that is part of a UNION operation. You can only order the whole set. In set theory, or relational algebra, it wouldn't make sense to order subselects anyway, as a set operation cannot guarantee order correctness. Often, you still want to do it, because you apply a LIMIT to every subselect. Let's say, you want to find the employees with the highest salary in every department in Postgres syntax:

SELECT * FROM (
  SELECT * FROM emp WHERE dept = 'IT'
  ORDER BY salary LIMIT 1
) UNION (
  SELECT * FROM emp WHERE dept = 'Marketing'
  ORDER BY salary LIMIT 1
) UNION (
  SELECT * FROM emp WHERE dept = 'R&D'
  ORDER BY salary LIMIT 1
)

create.selectFrom(EMP).where(DEPT.equal("IT"))
      .orderBy(SALARY).limit(1)
      .union(
create.selectFrom(EMP).where(DEPT.equal("Marketing"))
      .orderBy(SALARY).limit(1))
      .union(
create.selectFrom(EMP).where(DEPT.equal("R&D")
      .orderBy(SALARY).limit(1)))

There is a subtle difference between the above two queries. In SQL, every UNION subselect is in fact a nested SELECT, wrapped in parentheses. In this example, the notion of "nested SELECT" and "subselect" are slightly different.