Expression list

An expression list is a collection of expressions.

An expression list can appear in comparison and membership conditions, as well as in the GROUP BY clause of queries and subqueries. In comparison and membership conditions, an expression list is sometimes referred to as a row value constructor or row constructor.

Comparison and membership conditions appear in the WHERE clause. They can contain one or more comma-separated expressions, or a group or multiple groups of expressions, where each group contains one or more comma-separated expressions. The format requirements for multiple groups of expressions are as follows:

• Each group is enclosed in parentheses.

• Each group must contain the same number of expressions.

• The number of expressions in each group must match the number of expressions before the operator in a comparison condition or before the IN keyword in a membership condition.

A comma-separated expression list can contain a maximum of 1000 expressions. A comma-separated expression group list can contain any number of expression groups, but each expression group can contain a maximum of 1000 expressions.

The following examples show valid expression lists:

(100, 200, 300)
('SCOTT', 'BLAIR', 'MARK')
( ('fruit', 'apple', 'red'),('vegetable', 'eggplant', 'purple') )

In the third example, the number of expressions in each group must match the number of expressions in the first part of the SQL statement condition. For example:

SELECT * FROM products
    WHERE (category, name, color) IN
        ( ('fruit', 'apple', 'red'),('vegetable', 'eggplant', 'purple') );

In a simple GROUP BY clause, an expression list supports both uppercase and lowercase forms, as shown in the following example:

SELECT dept_id, MIN(salary) min, MAX(salary) max FROM emp
    GROUP BY dept_id, salary
    ORDER BY dept_id, min, max;

SELECT dept_id, MIN(salary) min, MAX(salary) max FROM emp
    GROUP BY (dept_id, salary)
    ORDER BY dept_id, min, max;

In the ROLLUP and GROUPING SETS subclauses of the GROUP BY clause, you can combine a single expression with an expression group in the same expression list. The following example shows valid grouping expression lists in an SQL statement:

SELECT prod_category, prod_subcategory, country_id, cust_city, count(*)
    FROM products, sales, customers
    WHERE sales.prod_id = products.prod_id
        AND sales.cust_id=customers.cust_id
        AND sales.time_id = '01-dec-00'
        AND customers.cust_year_of_birth BETWEEN 1970 and 1980
    GROUP BY GROUPING SETS (
        (prod_category, prod_subcategory, country_id, cust_city), (prod_category, prod_subcategory, country_id),        (prod_category, prod_subcategory),
        country_id
        )
    ORDER BY prod_category, prod_subcategory, country_id, cust_city;