Table of Contents

About

VALUES is a sql expression that construct:

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

This is a syntax that may not be implemented for every database

  • 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