Subquery Rules

A subquery is subject to a number of restrictions:

• The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively). If the WHERE clause of the outer statement includes a column name, it must be join-compatible with the column named in the subquery select list.
• The datatypes text and image are not allowed in the select list of subqueries.
• Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
• The DISTINCT keyword cannot be used with subqueries that include a GROUP BY clause.
• Because a subquery cannot include an ORDER BY clause, a COMPUTE clause, or an INTO keyword, it cannot manipulate its results internally. The optional DISTINCT keyword can effectively order the results of a subquery, because the system eliminates duplicates by ordering the results first.
• A view created with a subquery cannot be updated.
• The select list of a subquery introduced with EXISTS by convention consists of an asterisk (*) instead of the single column name. Do not specify more than one column name. The select list rules for a subquery introduced with EXISTS are identical to those for a standard select list because a subquery introduced with EXISTS constitutes an existence test and returns TRUE or FALSE rather than data.