2.4 Subqueries

Organization of this section
(1) Function
(2) Privileges
(3) Format
(4) Operands
(5) Rules on subqueries for a derived table in a predicate (IN predicate, comparison predicate, quantified predicate, or EXISTS predicate) or in a FROM clause
(6) Rules
(7) Specification examples

(1) Function

A subquery specifies the value, or the set of values, that can be obtained by retrieving a table.

The following types of subqueries are available:

Subqueries can be specified in the following locations:

Scalar subquery

Row subquery

Table subquery

(2) Privileges

A user with the SELECT privilege for a table can execute a subquery to retrieve that table.

(3) Format

scalar-subquery::=subquery
row-subquery::=subquery
table-subquery::=subquery

subquery::=([SQL-optimization-specification-for-subquery-execution-method]query-expression-body)

(4) Operands

For SQL optimization specification for a subquery execution method, see 2.24 SQL optimization specification. For details about a query specification body, see 2.2 Query expressions.

(5) Rules on subqueries for a derived table in a predicate (IN predicate, comparison predicate, quantified predicate, or EXISTS predicate) or in a FROM clause

  1. Value expressions that yield results of the following data types cannot be specified in a selection expression in a predicate or in a subquery in a derived table:
    • BLOB
    • BINARY with a minimum length of 32,001 bytes
    • Abstract data type
  2. If either * or table-specification.* is specified in a subquery in the EXISTS predicate, the specification means the following:
    • When a set operation is not specified:
    The specification means any one column that is allowed in the subquery other than a set function.
    • When a set operation is specified:
    The * specification means specifying all column in all the tables specified in the FROM clause of that query specification in the order of the tables specified in the FROM clause. In each table, columns are ordered in the sequence that was specified at the time of table definition.
    The table-specification.* specification means specifying all columns in all the specified tables in the order in which they were specified at the time of table definition.

(6) Rules

  1. The result of a subquery is a NOT NULL constraint (allows the NULL value). However, the results of a table subquery specified in a derived table in the FROM clause are subject to the same constraint as the results of that query expression.
  2. The maximum number of columns of the results of a row subquery specified in a row value constructor is 255.
  3. The maximum number of columns in the results of a row subquery specified in the SET clause of UPDATE is 30,000.
  4. The maximum number of columns in the results of a table subquery is 255. However, the maximum number of columns in the results of a table subquery for a derived table in the FROM clause is 30,000.
  5. When a scalar subquery or a row subquery is specified, the maximum allowable number of rows in the results is 1.
  6. If the results of a scalar subquery are zero rows, the result is the null value.
  7. If the results of a row subquery are zero rows, the result is a row in which all members are the null value.
  8. Value expressions that yield results with the following data types cannot be specified in a selection expression in a subquery:
    • BLOB
    • BINARY with a maximum length of 32,001 bytes or greater
    • Abstract data type
    • BOOLEAN
    The above restrictions, however, do not apply to a selection expression in a scalar subquery or a row subquery that is directly specified as an update value in the SET clause of the UPDATE statement.
  9. [table-specification.]ROW cannot be specified in a selection expression in a subquery.
  10. You cannot specify a WRITE specification, a GET_JAVA_STORED_ROUTINE_SOURCE specification, or the window function as a selection expression in a subquery.
  11. Unsubscripted repetition columns cannot be specified in a selection expression in a subquery.
    The above restriction, however, does not apply to selection expressions for a scalar subquery or a row subquery that is directly specified as an update value in the SET clause of the UPDATE statement.
  12. A subquery cannot be specified in an SQL statement for access to a foreign table. View tables containing a subquery cannot be specified on a mixed basis with a foreign table.
  13. A subquery cannot be specified in a value expression that is specified as an argument in a set function.

(7) Specification examples

  1. Specifying a table subquery in a quantified predicate in a SELECT statement:

    SELECT DISTINCT PNAME FROM STOCK
      WHERE SQUANTITY > ALL
      (SELECT SQUANTITY FROM STOCK
      WHERE PNAME = N'socks')

  2. Specifying a scalar subquery in the SET clause in an UPDATE statement:
    Change the column of stock level (SQUANTITY) with a product code (PCODE) column in the stock table (STOCK) is 302S to the stock level (SQUANTITY) of the product for which the product code (PCODE) column of stock table 2 (STOCK2) with the same column definition information as the stock table is 302S.

    UPDATE STOCK
     SET SQUANTITY =
       (SELECT SQUANTITY FROM STOCK2 WHERE PCODE = '302S')
     WHERE PCODE = '302S'

  3. Specifying a row subquery in the SET clause of an UPDATE statement
    Change the stock level (SQUANTITY) column and the unit price (PRICE) column of a stock table (STOCK) with a product code column (PCODE) of 302S to the stock level (SQUANTITY) and unit price (PRICE) of the product for which the product code (PCODE) column in stock table 2 (STOCK2) with the same column definition information as the stock table is 302S.

    UPDATE STOCK
     SET (PRICE,SQUANTITY) =
           (SELECT PRICE,SQUANTITY FROM STOCK2
             WHERE PCODE = '302S')