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
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
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
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
Do you have any feedback about this page? We'd love to hear it!