FROM VALUES: Table Value Constructors in Oracle 23ai

Oracle 23ai introduces table value constructors — a way to use a VALUES clause directly in a FROM clause, creating an inline result set without needing a real table, a UNION ALL chain, or a PL/SQL collection.

This is especially useful for testing queries, building lookup sets, or constructing small datasets on the fly.

Basic syntax:

SELECT *
FROM   (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol'))
       AS t(id, name);

Practical use case — joining against a small inline dataset:

SELECT e.employee_name, r.region_label
FROM   employees e
JOIN   (VALUES ('EMEA', 'Europe, Middle East & Africa'),
               ('APAC', 'Asia Pacific'),
               ('AMER', 'Americas'))
       AS r(region_code, region_label)
ON     e.region = r.region_code;

Why not just use DUAL with UNION ALL?

Before 23ai, the standard Oracle workaround was:

SELECT 1 AS id, 'Alice' AS name FROM DUAL
UNION ALL
SELECT 2, 'Bob'   FROM DUAL
UNION ALL
SELECT 3, 'Carol' FROM DUAL;

It works, but it’s verbose and scales poorly. With FROM VALUES, the syntax is cleaner and performs better because Oracle can optimize it as a single operation.

Testing aggregate functions inline:

SELECT region, SUM(sales) AS total
FROM   (VALUES ('EMEA', 500), ('EMEA', 300), ('APAC', 800))
       AS t(region, sales)
GROUP BY region;

This pattern is fantastic for unit-testing SQL logic without needing real data in real tables. Write your query against a known dataset, validate it, then swap in the real table.

Table value constructors are a mature feature in ANSI SQL and in most other major databases. Their arrival in Oracle 23ai is welcome — and makes a lot of common SQL patterns significantly cleaner.