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 an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
• The ntext, text and image data types 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 GROUP BY.
• The COMPUTE and INTO clauses cannot be specified.
• ORDER BY can only be specified if TOP is also specified.
• 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 a single column name. The 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.