VALUES as Table Expression

Database	Status	Syntax Family	Min Version
PostgreSQL	full	-	-
MySQL	full	values-row	8.0.19
MariaDB	full	-	10.3.3
Oracle	full	-	-
SQL Server	full	-	2008
SQLite	full	-	3.8.3
Redshift	full	-	-
DB2	full	values-inline-table	-
Snowflake	full	-	-
BigQuery	partial	-	-

Compare Databases

Database Details

PostgreSQL full

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

-- Join against an inline lookup table
SELECT o.order_id, s.label
FROM orders o
JOIN (VALUES ('pending', 'Awaiting'), ('shipped', 'In Transit'), ('done', 'Delivered')) AS s(status, label)
  ON o.status = s.status;

-- VALUES as a standalone query
VALUES (1, 'Alice'), (2, 'Bob');

Column names are assigned on the alias: AS t(col1, col2).
Standalone VALUES (1, 'x'), (2, 'y') is a complete query.

Docs: VALUES Lists, VALUES

MySQL full

-- Inline table using ROW() constructor (MySQL 8.0.19+)
SELECT *
FROM (VALUES ROW(1, 'Alice'), ROW(2, 'Bob'), ROW(3, 'Carol')) AS t(id, name);

-- Standalone VALUES statement
VALUES ROW(1, 'Alice'), ROW(2, 'Bob');

Added in 8.0.19. Each row must be wrapped in ROW(...); bare tuples are rejected.
Default column names in the alias are column_0, column_1, ... unless overridden.

Docs: VALUES Statement

MariaDB full

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

-- Standalone VALUES statement
VALUES (1, 'Alice'), (2, 'Bob');

Added in 10.3.3. Uses the plain tuple form, not MySQL's ROW(...) wrapper.

Docs: Table Value Constructors, VALUES

Oracle full

-- Inline table in FROM
SELECT *
FROM (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol')) t(id, name);

-- VALUES in a CTE
WITH lookup(id, name) AS (
  VALUES (1, 'Alice'), (2, 'Bob')
)
SELECT * FROM lookup;

Older Oracle code emulated VALUES with SELECT ... FROM DUAL UNION ALL; that is no longer needed on current releases.
DUAL still exists for single-row expression evaluation.

Docs: VALUES, SELECT

SQL Server full

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

-- Join against an inline lookup table
SELECT o.order_id, s.label
FROM orders o
JOIN (VALUES ('pending', 'Awaiting'), ('shipped', 'In Transit'), ('done', 'Delivered')) AS s(status, label)
  ON o.status = s.status;

Also called table value constructors in the docs. Same tuple-form syntax as PostgreSQL.

Docs: Table Value Constructor (Transact-SQL)

SQLite full

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

-- Standalone VALUES statement
VALUES (1, 'Alice'), (2, 'Bob');

Available since 3.8.3 (2014-02-03).

Docs: SELECT - WITH clause and VALUES

Redshift full

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

-- Use in a CTE
WITH lookup AS (
  VALUES (1, 'Alice'), (2, 'Bob')
)
SELECT * FROM lookup AS t(id, name);

Same syntax as PostgreSQL.

Docs: VALUES

DB2 full

-- Inline table using VALUES in FROM
SELECT *
FROM (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol')) AS t(id, name);

-- Standalone VALUES statement
VALUES (1, 'Alice'), (2, 'Bob');

Long-standing. Standalone VALUES is also a complete query.

Docs: VALUES statement

Snowflake full

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

-- Standalone VALUES statement
VALUES (1, 'Alice'), (2, 'Bob');

Docs: VALUES

BigQuery partial

-- Workaround: UNNEST with STRUCT array
SELECT *
FROM UNNEST([
  STRUCT(1 AS id, 'Alice' AS name),
  STRUCT(2 AS id, 'Bob' AS name),
  STRUCT(3 AS id, 'Carol' AS name)
]);

-- Workaround: UNION ALL of SELECT subqueries
SELECT 1 AS id, 'Alice' AS name
UNION ALL SELECT 2, 'Bob'
UNION ALL SELECT 3, 'Carol';

No VALUES in FROM. The idiomatic substitute is UNNEST([STRUCT(...), STRUCT(...)]); UNION ALL of single-row SELECTs also works but is verbose.

Docs: UNNEST