VALUES CLAUSEQuery Techniques
Buy Me A Coffee

Construct an inline table from literal values without referencing a persistent table. Useful for multi-row inserts, testing, and constructing ad-hoc result sets to join against.

Generates a derived table of literal values, useful for defining small lookup tables on the fly without creating a temporary table. SELECT * FROM (VALUES (1, a), (2, b)) AS t(id, name) is portable and useful in INSERT and JOIN contexts.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0.19 Multi-row INSERT VALUES has been supported since early MySQL versions. The TABLE and VALUES statement as a standalone query (VALUES ROW(...), ROW(...)) was added in MySQL 8.0.19, providing an inline table construct. Prior to 8.0.19, use UNION ALL of SELECT literals to simulate an inline table.
PostgreSQL ✓ Supported 8.2 VALUES is a full first-class relation since PostgreSQL 8.2 — it can appear anywhere a table or subquery can: in FROM, CTE, UNION, INSERT, etc. Column names in the alias are required when the VALUES clause is used as a subquery or CTE. Row type is inferred from the first row; explicit casts may be needed for ambiguous literals.
SQL Server ✓ Supported 2008 VALUES as an inline table constructor available since SQL Server 2008. Syntax requires an alias with column names. Often used for multi-row inserts and lookup tables in CTEs. Cannot exceed 1000 rows in a single VALUES constructor.
Oracle ✗ Not Supported — Oracle does not support a VALUES clause as a standalone table constructor in SELECT queries. Multi-row INSERT is supported via INSERT ALL or INSERT with a SELECT from DUAL for a single row. For an inline lookup table, use a UNION ALL SELECT from DUAL, or a collection type with TABLE().
SQLite ✓ Supported 3.8.3 VALUES as a table-value constructor in SELECT/FROM supported since SQLite 3.8.3. Supports alias with column names. Fully usable in CTEs, FROM clauses, and UNION.

Details

Multi-row INSERT VALUES (syntax: INSERT INTO t VALUES (r1), (r2), ...) is broadly supported across all engines. The more interesting case is VALUES as a standalone table constructor usable in SELECT, FROM, CTEs, and JOINs — this works in PostgreSQL (8.2+), SQL Server (2008+), SQLite (3.8.3+), and MySQL (8.0.19+ with ROW() wrapper). Oracle is the outlier: it has no VALUES table constructor and requires UNION ALL of SELECT ... FROM DUAL as the workaround. The MySQL VALUES ROW() syntax differs from all other engines — other engines use plain VALUES (row), (row) without the ROW() keyword.

Standard Syntax

-- Inline table of literal values: SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol')) AS t(id, name); -- Multi-row INSERT: INSERT INTO colors (id, name) VALUES (1, 'red'), (2, 'green'), (3, 'blue');

Version Support

MySQL: Since 8.0.19 PostgreSQL: Since 8.2 SQL Server: Since 2008 Oracle: Not supported SQLite: Since 3.8.3

Per-Database Syntax & Notes

MySQL

Multi-row INSERT VALUES has been supported since early MySQL versions. The TABLE and VALUES statement as a standalone query (VALUES ROW(...), ROW(...)) was added in MySQL 8.0.19, providing an inline table construct. Prior to 8.0.19, use UNION ALL of SELECT literals to simulate an inline table.

-- Multi-row INSERT (supported since early versions): INSERT INTO colors (id, name) VALUES (1, 'red'), (2, 'green'), (3, 'blue'); -- Standalone VALUES statement (8.0.19+): VALUES ROW(1, 'Alice'), ROW(2, 'Bob'); -- Inline table via derived query (pre-8.0.19 workaround): SELECT * FROM ( SELECT 1 AS id, 'Alice' AS name UNION ALL SELECT 2, 'Bob' UNION ALL SELECT 3, 'Carol' ) AS t; -- JOIN against inline values: SELECT p.*, v.label FROM products p JOIN (VALUES ROW(1,'small'), ROW(2,'medium'), ROW(3,'large')) AS v(id,label) ON p.size_id = v.id;

PostgreSQL

VALUES is a full first-class relation since PostgreSQL 8.2 — it can appear anywhere a table or subquery can: in FROM, CTE, UNION, INSERT, etc. Column names in the alias are required when the VALUES clause is used as a subquery or CTE. Row type is inferred from the first row; explicit casts may be needed for ambiguous literals.

-- Inline table: SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob')) AS t(id, name); -- CTE using VALUES: WITH priority_map(level, label) AS ( VALUES (1, 'low'), (2, 'medium'), (3, 'high') ) SELECT o.id, pm.label FROM orders o JOIN priority_map pm ON pm.level = o.priority; -- UNION with VALUES: SELECT id, name FROM customers UNION ALL VALUES (0, 'Anonymous'); -- Multi-row INSERT: INSERT INTO colors (id, name) VALUES (1, 'red'), (2, 'green'), (3, 'blue');

SQL Server

VALUES as an inline table constructor available since SQL Server 2008. Syntax requires an alias with column names. Often used for multi-row inserts and lookup tables in CTEs. Cannot exceed 1000 rows in a single VALUES constructor.

-- Inline table: SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol')) AS t(id, name); -- JOIN against inline values: SELECT p.*, v.label FROM products p JOIN (VALUES (1,'S'),(2,'M'),(3,'L')) AS v(size_id, label) ON p.size_id = v.size_id; -- CTE with VALUES: WITH codes(code, description) AS ( VALUES ('A','Active'),('I','Inactive'),('P','Pending') ) SELECT u.name, c.description FROM users u JOIN codes c ON u.status = c.code; -- Multi-row INSERT: INSERT INTO colors (id, name) VALUES (1,'red'),(2,'green'),(3,'blue');

Oracle

Oracle does not support a VALUES clause as a standalone table constructor in SELECT queries. Multi-row INSERT is supported via INSERT ALL or INSERT with a SELECT from DUAL for a single row. For an inline lookup table, use a UNION ALL SELECT from DUAL, or a collection type with TABLE().

-- Single-row INSERT (Oracle standard): INSERT INTO colors (id, name) VALUES (1, 'red'); -- Multi-row INSERT via INSERT ALL: INSERT ALL INTO colors (id, name) VALUES (1, 'red') INTO colors (id, name) VALUES (2, 'green') INTO colors (id, name) VALUES (3, 'blue') SELECT 1 FROM DUAL; -- Inline table workaround (UNION ALL from DUAL): SELECT * FROM ( SELECT 1 AS id, 'Alice' AS name FROM DUAL UNION ALL SELECT 2, 'Bob' FROM DUAL UNION ALL SELECT 3, 'Carol' FROM DUAL ) t;

SQLite

VALUES as a table-value constructor in SELECT/FROM supported since SQLite 3.8.3. Supports alias with column names. Fully usable in CTEs, FROM clauses, and UNION.

-- Inline table: SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob')) AS t(id, name); -- CTE with VALUES: WITH priority(level, label) AS ( VALUES (1,'low'),(2,'medium'),(3,'high') ) SELECT * FROM priority; -- Multi-row INSERT: INSERT INTO colors (id, name) VALUES (1,'red'),(2,'green'),(3,'blue');