Expression list

An expression list is a group of expressions.

You can use an expression list in a comparison or membership condition or a GROUP clause of a query or subquery. An expression list in a comparison or membership condition is sometimes called a row value constructor or row constructor.

You can use a comparison or membership condition in a WHERE clause. They can contain one or more expressions separated by commas, or one or more groups of expressions, where each group contains one or more expressions separated by commas. To use multiple expression groups as a condition, observe the following requirements:

• Each group is enclosed in brackets.

• 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 the comparison condition, or match the number of expressions before the IN keyword in the membership condition.

A expression list can contain a maximum of 1,000 expressions separated by commas. A expression group list can contain any number of expression groups separated by commas. However, each expression group can contain a maximum of 1,000 expressions separated by commas.

The following shows examples of 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 be the same as the number of expressions in the first part of the condition in the SQL statement. Example:

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

An expression list in a simple GROUP clause can be in uppercase or lowercase, for 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 a ROLLUP, CUBE, and GROUPING SETS clause of the GROUP BY clause, you can use both single expressions and expression groups in one expression list. The following shows examples of valid group expression lists in SQL statements:

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;