# VALUES expression

The VALUES expression allows construction of a row or a table from other values. You use a VALUES statement when you do not have a FROM clause. This construct can be used in all the places where a query can, and thus can be used as a statement that returns a ResultSet, within expressions and statements wherever subqueries are permitted, and as the source of values for an INSERT statement.

#### Syntax

    {
        VALUES ( Value {, Value }* )
            [ , ( Value {, Value }* ) ]* |
        VALUES Value [ , Value ]* |
     }

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.

#### 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)
    -- 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)

- Value

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
WHERE clause
WHERE CURRENT OF clause