<< The DEFAULT keyword | Boolean expressions >>

Expression precedence

<< The DEFAULT keyword | Boolean expressions >>

Derby Reference Manual

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)

Expression precedence

Precedence of operations from highest to lowest is:

· (), ?, Constant (including sign), NULL, ColumnReference, ScalarSubquery, CAST
· LENGTH, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, and

other built-ins

· unary + and -
· *, /, || (concatenation)
· binary + and -
· comparisons, quantified comparisons, EXISTS, IN, IS NULL, LIKE, BETWEEN, IS
· NOT
· AND
· OR

You can explicitly specify precedence by placing expressions within parentheses.
An expression within parentheses is evaluated before any operations outside the
parentheses are applied to it.

Example

(3+4)*9