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.
The VALUES() table constructor
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases allow for expressing in-memory temporary tables using a VALUES() constructor. This constructor usually works the same way as the VALUES() clause known from the INSERT statement or from the MERGE statement. With jOOQ, you can also use the VALUES() table constructor, to create tables that can be used in a SELECT statement's FROM clause:
SELECT a, b FROM VALUES(1, 'a'), (2, 'b') t(a, b)
create.select() .from(values(row(1, "a"), row(2, "b")).as("t", "a", "b")) .fetch(); Note, that it is usually quite useful to provide column aliases ("derived column lists") along with the table alias for the VALUES() constructor.
Dialect support
This example using jOOQ:
selectFrom(values(row(1, "a"), row(2, "b")).as("t", "a", "b")) Translates to the following dialect specific expressions:
Access
SELECT t.a, t.b FROM ( SELECT 1 a, 'a' b FROM ( SELECT count(*) dual FROM MSysResources ) AS dual UNION ALL SELECT 2, 'b' FROM ( SELECT count(*) dual FROM MSysResources ) AS dual ) t
ASE, Redshift, SQLDataWarehouse, Vertica
SELECT t.a, t.b FROM ( SELECT 1, 'a' UNION ALL SELECT 2, 'b' ) t (a, b)
Aurora MySQL, MemSQL
SELECT t.a, t.b FROM ( SELECT 1 a, 'a' b FROM DUAL UNION ALL SELECT 2, 'b' FROM DUAL ) t
Aurora Postgres, CockroachDB, DB2, Databricks, DuckDB, Exasol, H2, HSQLDB, Oracle, Postgres, SQLServer, Snowflake, Trino, YugabyteDB
SELECT t.a, t.b FROM ( VALUES (1, 'a'), (2, 'b') ) t (a, b)
BigQuery
SELECT t.a, t.b FROM ( SELECT null a, null b FROM UNNEST([STRUCT(1 AS dual)]) AS dual WHERE FALSE UNION ALL SELECT * FROM UNNEST ([ STRUCT (1, 'a'), STRUCT (2, 'b') ]) t ) t
ClickHouse, MariaDB, Spanner
SELECT t.a, t.b FROM ( SELECT 1 a, 'a' b UNION ALL SELECT 2, 'b' ) t
Firebird
SELECT t.a, t.b FROM ( SELECT 1, 'a' FROM RDB$DATABASE UNION ALL SELECT 2, 'b' FROM RDB$DATABASE ) t (a, b)
Hana
SELECT t.a, t.b FROM ( SELECT 1 a, 'a' b FROM SYS.DUMMY UNION ALL SELECT 2, 'b' FROM SYS.DUMMY ) t
Informix
SELECT t.a, t.b FROM ( TABLE (MULTISET { ROW (1, 'a'), ROW (2, 'b') }) ) t (a, b) MySQL
SELECT t.a, t.b FROM ( VALUES ROW (1, 'a'), ROW (2, 'b') ) t (a, b)
SQLite
SELECT t.a, t.b FROM ( SELECT null a, null b WHERE 1 = 0 UNION ALL SELECT * FROM ( VALUES (1, 'a'), (2, 'b') ) t ) t
Sybase
SELECT t.a, t.b FROM ( SELECT 1, 'a' FROM SYS.DUMMY UNION ALL SELECT 2, 'b' FROM SYS.DUMMY ) t (a, b)
Teradata
SELECT t.a, t.b FROM ( SELECT 1, 'a' FROM ( SELECT 1 AS "dual" ) AS "dual" UNION ALL SELECT 2, 'b' FROM ( SELECT 1 AS "dual" ) AS "dual" ) t (a, b)
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!