All Products
Search
Document Center

Expression lists

Last Updated: Jun 18, 2021

An expression list is a group of other expressions.

Expression lists can appear in comparison and membership conditions and in the GROUP BY clauses of queries and subqueries. Expression lists in comparison and membership conditions are called row value constructors or row constructors.

Comparison and membership conditions appear in the WHERE clauses. Comparison and membership conditions can contain one expression or multiple expressions that are separated by commas (,), or one or more groups of expressions. Each group of expressions contains one expression or multiple expressions that are separated by commas (,). In the following examples (multiple groups of expressions):

  • 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 that precede the operator in the comparison condition. Alternatively, the number of expressions in each group must match the number of expressions that precede the IN keyword in the membership condition.

A comma-separated list of expressions can contain a maximum of 1,000 expressions. A comma-separated list of groups of expressions can contain expression groups whose quantity is not limited. However, each expression group can contain a maximum of 1,000 expressions.

The following examples show some valid expression lists:

(10, 20, 40)
('SCOTT', 'BLAKE', 'TAYLOR')
( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )

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 of the SQL statement. The following example shows the corresponding statement:

SELECT * FROM employees
    WHERE (first_name, last_name, email) IN
        (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'));

In a simple GROUP BY clause, you can use the uppercase or lowercase expression list.

SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees 
    GROUP BY department_id, salary
    ORDER BY department_id, min, max;

SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees 
    GROUP BY (department_id, salary)
    ORDER BY department_id, min, max;

In the ROLLUP, CUBE, and GROUPING SETS clauses of a GROUP BY clause, you can combine individual expressions and expression groups in the same expression list. The following example shows some valid expression group lists in the 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-oct-00'
        AND customers.cust_year_of_birth BETWEEN 1960 and 1970
    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;