SQL VALUES

What is the SQL VALUES clause used for?

VALUES creates in-line row value lists that can be inserted into a table or returned as a result set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VALUES: PostgreSQL, MySQL 8.0+, MariaDB 10.3+, SQL Server, Oracle, SQLite, DuckDB, Snowflake

SQL VALUES Full Explanation

The VALUES clause is the ANSI-SQL way to construct one or more rows directly in a statement. It is most often paired with INSERT to add data into a table, but it can also be used as a stand-alone table expression in SELECT queries or common table expressions (CTEs). Each set of parentheses defines a single row. Columns are ordered positionally, and every row must contain the same number of expressions. Expressions can be literals, scalar functions, NULL, DEFAULT (inside INSERT), or parameters. When used outside INSERT, VALUES returns an ephemeral result set that can be joined, filtered, or unioned like any other subquery. Because VALUES is evaluated once per statement, it is faster and more readable than multiple UNION ALL literals. Most modern databases support multi-row VALUES, but some older versions only allow a single row per INSERT.

SQL VALUES Syntax

Standalone:
VALUES (value1 [, value2 ...])
       [, (value1 [, value2 ...]) ...];

INSERT:
INSERT INTO table_name [(column1, column2, ...)]
VALUES (value1 [, value2 ...])
       [, (value1 [, value2 ...]) ...];

SQL VALUES Parameters

value_expr - any scalar expression evaluated for the column position
DEFAULT - keyword that inserts the column's default value (INSERT only)

Example Queries Using SQL VALUES

--Standalone VALUES as a derived table
SELECT *
FROM (VALUES (1, 'alpha'),
             (2, 'beta')) AS t(id, label);

--Multi-row INSERT
INSERT INTO users (id, name, role)
VALUES (101, 'Ada', 'admin'),
       (102, 'Grace', 'editor');

--Using DEFAULT in INSERT
INSERT INTO orders (order_id, created_at, status)
VALUES (DEFAULT, NOW(), 'pending');

Expected Output Using SQL VALUES

First query returns two rows with columns id and label
Second query inserts two new rows into users and returns insert count
Third query inserts one row, letting the database generate order_id

Use Cases with SQL VALUES

Bulk inserting small data sets without loading from files
Seeding lookup tables in migration scripts
Creating on-the-fly dimension tables for joins in analytical queries
Replacing UNION ALL of literals for readability and speed

Common Mistakes with SQL VALUES

Mismatching column count between VALUES rows or table columns
Forgetting parentheses around each row
Using VALUES outside INSERT without aliasing in databases that require it (e.g., SQL Server)
Mixing data types in the same column position causing implicit conversion errors

First Introduced In

SQL-92

Frequently Asked Questions

How is VALUES different from UNION ALL?

VALUES is optimized for defining literal rows inline and usually parses faster than chaining SELECT ... UNION ALL.

Do I need to alias a standalone VALUES expression?

Yes. Most dialects (PostgreSQL, SQL Server, etc.) require an alias when VALUES appears in the FROM clause or a CTE.

Can I mix data types inside VALUES?

All expressions in the same column position must be implicitly castable to a common type. Otherwise the database raises a type error.

Does MySQL support multi-row VALUES inserts?

Yes, starting with MySQL 8.0 you can supply multiple row constructors in a single INSERT statement.