Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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.
Differences to standard SQL
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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 set operation subselect may not immediately 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
Dialect support
This example using jOOQ:
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).limit(1).union(select(AUTHOR.ID).from(AUTHOR).orderBy(AUTHOR.ID).limit(1)).orderBy(1)
Translates to the following dialect specific expressions:
Access, SQLDataWarehouse, Sybase
( SELECT TOP 1 BOOK.ID FROM BOOK ORDER BY BOOK.ID ) UNION ( SELECT TOP 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) ORDER BY 1
ASE, SQLServer
SELECT * FROM ( SELECT TOP 1 BOOK.ID FROM BOOK ORDER BY BOOK.ID ) x UNION SELECT * FROM ( SELECT TOP 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) x ORDER BY 1
Aurora MySQL, Aurora Postgres, CockroachDB, DuckDB, Exasol, HSQLDB, Hana, MySQL, Redshift, Snowflake, Vertica, YugabyteDB
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ORDER BY 1
BigQuery, Spanner
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION DISTINCT ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ORDER BY 1
ClickHouse
SELECT t.* FROM ( ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION DISTINCT ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ) t ORDER BY 1
Databricks
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT CAST(1 AS int) ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT CAST(1 AS int) ) ORDER BY 1
DB2, Firebird, Oracle
SELECT * FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID FETCH NEXT 1 ROWS ONLY ) x UNION SELECT * FROM ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID FETCH NEXT 1 ROWS ONLY ) x ORDER BY 1
Derby, H2, MariaDB, Postgres, Trino
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID FETCH NEXT 1 ROWS ONLY ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID FETCH NEXT 1 ROWS ONLY ) ORDER BY 1
Informix
SELECT * FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID ) x UNION SELECT * FROM ( SELECT * FROM ( SELECT FIRST 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) x ) x ORDER BY 1
MemSQL
SELECT t.* FROM ( ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ) t ORDER BY 1
SQLite
SELECT * FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) x UNION SELECT * FROM ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) x ORDER BY 1
Teradata
( SELECT TOP 1 BOOK.ID FROM BOOK ORDER BY BOOK.ID ) UNION ( SELECT * FROM ( SELECT TOP 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) x ) ORDER BY 1
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!