Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Subquery Rules

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.

Community Additions

© 2015 Microsoft