Subquery Rules
A subquery is subject to the following 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 cannot be used 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 when TOP is also specified.
A view created by using a subquery cannot be updated.
The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.