Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
UNION
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A UNION operation combines two subquery results of compatible row type into a single result. 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, see also this section of the manual. 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();
Dialect support
This example using jOOQ:
select(BOOK.ID).from(BOOK).union(select(AUTHOR.ID).from(AUTHOR)).orderBy(BOOK.ID)
Translates to the following dialect specific expressions:
ASE, Access, Aurora MySQL, Aurora Postgres, CockroachDB, DB2, Derby, DuckDB, Exasol, H2, HSQLDB, Hana, Informix, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
SELECT BOOK.ID FROM BOOK UNION SELECT AUTHOR.ID FROM AUTHOR ORDER BY ID
BigQuery
SELECT BOOK.ID FROM BOOK UNION DISTINCT SELECT AUTHOR.ID FROM AUTHOR ORDER BY ID
ClickHouse
SELECT t.* FROM ( SELECT BOOK.ID FROM BOOK UNION DISTINCT SELECT AUTHOR.ID FROM AUTHOR ) t ORDER BY ID
Firebird
SELECT BOOK.ID FROM BOOK UNION SELECT AUTHOR.ID FROM AUTHOR ORDER BY 1
MemSQL
SELECT t.* FROM ( SELECT BOOK.ID FROM BOOK UNION SELECT AUTHOR.ID FROM AUTHOR ) t ORDER BY ID
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!