Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Differences to standard SQL
Applies to ✅ 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, ASE, 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
-- AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DUCKDB, EXASOL, HANA, HSQLDB, 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
(
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
-- DB2
(
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
)
UNION (
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
FETCH NEXT 1 ROWS ONLY
)
ORDER BY 1
-- DERBY, H2, MARIADB, ORACLE, 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
-- FIREBIRD
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 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
)
UNION (
SELECT *
FROM (
SELECT FIRST 1 AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
) 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
-- SQLSERVER
(
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY
)
UNION (
SELECT TOP 1 AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
)
ORDER BY 1
-- TERADATA
(
SELECT 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
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!