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
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.
The above statement is emulated by jOOQ for those databases that do not support the VALUES() constructor, natively (actual emulations may vary):
-- If derived column expressions are supported: SELECT a, b FROM ( SELECT 1, 'a' FROM DUAL UNION ALL SELECT 2, 'b' FROM DUAL ) t(a, b) -- If derived column expressions are not supported: SELECT a, b FROM ( -- An empty dummy record is added to provide column names for the emulated derived column expression SELECT NULL a, NULL b FROM DUAL WHERE 1 = 0 UNION ALL -- Then, the actual VALUES() constructor is emulated SELECT 1, 'a' FROM DUAL UNION ALL SELECT 2, 'b' FROM DUAL ) t
Feedback
Do you have any feedback about this page? We'd love to hear it!