A subquery is a SELECT statement nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A statement that includes a subquery operates on rows from one table based on its evaluation of the subquery's SELECT statement. The subquery SELECT statement can refer to the same table as the outer query or to a different table. In Transact-SQL, a subquery that returns a single value can also be used anywhere an expression is allowed.
SELECT statements that contain one or more subqueries are sometimes called nested queries or nested SELECT statements. The practice of nesting one SELECT statement inside another one reason for the word "structured" in structured query language (SQL).
Many SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. Some SQL users prefer subqueries to alternative formulations. Other people prefer joins. In Transact-SQL, there are usually not performance differences between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join will yield better performance, since otherwise the nested query must be processed for each result of the outer query in order to ensure duplicates are eliminated. In such cases, a join approach would yield better results.
This chapter introduces subquery syntax and rules and discusses keywords and comparison operators used with subqueries. It also covers correlated subqueries, subqueries that cannot be evaluated independently but that depend on the outer query for their results, and subqueries introduced with EXISTS.