VALUES expression

The VALUES expression allows construction of a row or a table from other values.

A VALUES expression can be used in all the places where a query can, and thus can be used in any of the following ways:

As a statement that returns a ResultSet
Within expressions and statements wherever subqueries are permitted
As the source of values for an INSERT statement (in an INSERT statement, you normally use a VALUES expression when you do not use a selectExpression)

Syntax

{
    VALUES ( value [ , value ]* )
        [ , ( value [ , value ]* ) ]*
  |
    VALUES value [ , value ]*
} [ ORDER BY clause ]
  [ result offset clause ]
  [ fetch first clause ]

where value is defined as

expression | DEFAULT

The first form constructs multi-column rows. The second form constructs single-column rows, each expression being the value of the column of the row.

The DEFAULT keyword is allowed only if the VALUES expression is in an INSERT statement. Specifying DEFAULT for a column inserts the column's default value into the column. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table.

A VALUES expression that is used in an INSERT statement cannot use an ORDER BY, result offset, or fetch first clause. However, if the VALUES expression does not contain the DEFAULT keyword, the VALUES clause can be put in a subquery and ordered, as in the following statement:

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

Examples

-- 3 rows of 1 column
VALUES (1),(2),(3)
-- 3 rows of 1 column
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)
-- using ORDER BY and FETCH FIRST
VALUES (3,21),(1,22),(2,23) ORDER BY 1 FETCH FIRST 2 ROWS ONLY
-- using ORDER BY and OFFSET
VALUES (3,21),(1,22),(2,23) ORDER BY 1 OFFSET 1 ROW
-- constructing a derived table
VALUES ('orange', 'orange'), ('apple', 'red'),
('banana', 'yellow')
-- Insert two new departments using one statement into the DEPARTMENT table, 
-- but do not assign a manager to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
  VALUES ('B11', 'PURCHASING', 'B01'),
    ('E41', 'DATABASE ADMINISTRATION', 'E01')
-- insert a row with a DEFAULT value for the MAJPROJ column
INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE, MAJPROJ) 
VALUES ('PL2101', 'ENSURE COMPAT PLAN', 'B01', '000020', CURRENT_DATE, DEFAULT)

-- using a built-in function
VALUES CURRENT_DATE
-- getting the value of an arbitrary expression
VALUES (3*29, 26.0E0/3)
-- getting a value returned by a built-in function
values char(1)