A subquery is subject to these 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. You can use an asterisk (*) in a nested select statement that is not an exists subquery.
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. SQL derived tables can be used in the from clause of a subquery wherever the subquery is used
You cannot use subqueries in an order by, group by, or compute by list.
You cannot include a for browse clause in a subquery.
You cannot include a union clause in a subquery unless it is part of a derived table expression within the subquery. For more information on using SQL derived tables, see Chapter 9, “SQL-Derived Tables.”
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.
You cannot include text, unitext, or image datatypes 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 50 nesting levels.
The maximum number of subqueries on each side of a union is 50.
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. See Chapter 4, “Expressions, Identifiers, and Wildcard “Characters,” in the Reference Manual: Building Blocks.
