2.4 Subqueries
(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:
- Scalar subquery
A scalar subquery is a subquery that yields a result column count of 1 and a result row count of 1 or less.
- Row subquery
A row subquery is a subquery that yields a result column count of 2 or greater and a result row count of 1 or less.
- Table subquery
A table subquery is a subquery that yields a result column count of 1 or greater, and a result row count of 0 or greater.
Subqueries can be specified in the following locations:
Scalar subquery
- Anywhere a value expression can be specified
Row subquery
- Anywhere a row value constructor can be specified
- SET clause in the UPDATE statement
Table subquery
- Right-hand side of the IN predicate
- Right-hand side of a quantified predicate
- EXISTS predicate
- Derived table in the FROM clause
(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
(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
- 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
- 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
- The results of the subquery is returned without the NOT NULL constraint (allowing null values). 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.
- The maximum number of columns of the results of a row subquery specified in a row value constructor is 255.
- The maximum number of columns in the results of a row subquery specified in the SET clause of UPDATE is 30,000.
- 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.
- When a scalar subquery or a row subquery is specified, the maximum allowable number of rows in the results is 1.
- If the results of a scalar subquery are zero rows, the result is the null value.
- If the results of a row subquery are zero rows, the result is a row in which all members are the null value.
- 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.
An XMLAGG set function can be specified as an XML type value expression in a selection expression in a scalar subquery that is specified in the value expression of an XML query variable of an XMLQUERY function.
- [table-specification.]ROW cannot be specified in a selection expression in a subquery.
- You cannot specify a WRITE specification, a GET_JAVA_STORED_ROUTINE_SOURCE specification, or the window function as a selection expression in a subquery.
- 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.
- A subquery cannot be specified in a value expression that is specified as an argument in a set function.
(7) Specification examples
- 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')
- 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'
- 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')