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
UNION ALL
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A UNION ALL operation combines two subquery results of compatible row type into a single result without removing duplicates. 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 ALL 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).unionAll(select(AUTHOR.ID).from(AUTHOR)).orderBy(BOOK.ID)
Translates to the following dialect specific expressions:
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, DB2, Databricks, 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 ALL SELECT AUTHOR.ID FROM AUTHOR ORDER BY ID
ClickHouse, MemSQL
SELECT t.* FROM ( SELECT BOOK.ID FROM BOOK UNION ALL SELECT AUTHOR.ID FROM AUTHOR ) t ORDER BY ID
Firebird
SELECT BOOK.ID FROM BOOK UNION ALL SELECT AUTHOR.ID FROM AUTHOR 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!