A subquery is subject to the following restrictions:
The subquery_select_list can consist of only one column name, except in the exists subquery, where an (*) is usually used in place of the single column name. Do not specify more than one column name. Qualify column names with table or view names if there is ambiguity about the table or view to which they belong.
Subqueries can be nested inside the where or having clause of an outer select, insert, update, or delete statement, inside another subquery, or in a select list. Alternatively, you can write many statements that contain subqueries as joins; Adaptive Server processes such statements as joins.
In Transact-SQL, a subquery can appear almost anywhere an expression can be used, if it returns a single value.
You cannot use subqueries in an order by, group by, or compute by list.
You cannot include a for browse clause or a union in a subquery.
The select list of an inner subquery introduced with a comparison operator can include only one expression or column name, and the subquery must return a single value. The column you name in the where clause of the outer statement must be join-compatible with the column you name in the subquery select list.
text and image datatypes are not allowed in subqueries.
Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword.
Correlated (repeating) subqueries are not allowed in the select clause of an updatable cursor defined by declare cursor.
There is a limit of 16 nesting levels.
The maximum number of subqueries on each side of a union is 16.
The where clause of a subquery can contain an aggregate function only if the subquery is in a having clause of an outer query and the aggregate value is a column from a table in the from clause of the outer query.
The result expression from a subquery is subject to the same limits as for any expression. The maximum length of an expression is 16K. For more information, see Chapter 4, “Expressions, Identifiers, and Wildcard Characters”of the Adaptive Server Reference Manual.
