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
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:
-- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DB2, DERBY, DUCKDB, EXASOL, H2, HANA, HSQLDB, INFORMIX, -- MARIADB, MYSQL, ORACLE, POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, 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 -- 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
(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!