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
The VALUES() table constructor
Applies to ✅ 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
t.*
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
) 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
t.*
FROM (
SELECT
1 a,
'a' b
FROM DUAL
UNION ALL
SELECT 2, 'b'
FROM DUAL
) t
) t
Aurora Postgres, CockroachDB, DB2, Derby, 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
SELECT t.a, t.b
FROM (
SELECT
t.*
FROM (
SELECT
1 a,
'a' b
UNION ALL
SELECT 2, 'b'
) t
) 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
t.*
FROM (
SELECT
1 a,
'a' b
FROM SYS.DUMMY
UNION ALL
SELECT 2, 'b'
FROM SYS.DUMMY
) t
) 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.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!