1.0

The Query in a TableSubquery appearing in a FromItem can contain multiple columns and return multiple rows.
Example
-- SELECT from a Join expression
select C.CID, C.CUST_NAME, F.QTY from trade.customers C
LEFT JOIN trade.portfolio F on C.cid= F.cid where F.tid
= ?
VALUES Expression
The VALUES expression allows construction of a row or a table from other values.
Syntax
{
VALUES ( Value {, Value }* )
[ , ( Value {, Value }* ) ]* |
VALUES Value [ , Value ]*
}
where Value is defined as:
Expression | DEFAULT
Description
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 )
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.
Example
-- 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 customers using one statement into the
CUSTOMER table,
-- but do not assign value to the SINCE column.
INSERT INTO TRADE.CUSTOMERS (CID, CUST_NAME, ADDR,TID)
499
SQL Language Reference