What is the SQL VALUES word?

About

VALUES is a sql expression that construct:

• a row
• or a table

A VALUES expression can be used in all the places where a query can:

• As the source of values for an INSERT statement
• Within expressions and statements wherever subqueries are permitted
• (Java) As a statement that returns a Jdbc ResultSet

Syntax

• 3 rows of 1 column

VALUES (1),(2),(3)
VALUES 1, 2, 3 

• 1 row of 3 columns

VALUES (1, 2, 3)

• 3 rows of 2 columns

VALUES (1,21),(2,22),(3,23)

Example

JavaDb / Derby

Apache Derby (JavaDB) Reference

• Insert into

create table t(t char(1));
INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;

• Select

SELECT *
FROM (VALUES (1,'Unary'),(2,'Binary'),(3,'Ternary'))
AS arity(id,description)

Sql Server

SELECT *
FROM (VALUES (1,'Unary'),(2,'Binary'),(3,'Ternary'))
AS arity(id,description)

1	Unary
2	Binary
3	Ternary