Subqueries: Queries Within Other Queries

A subquery is a select statement that is nested inside another select, insert, update, or delete statement, inside a conditional statement, or inside another subquery.

You can also express subqueries as join operations.

Subqueries, also called inner queries, appear within a where or having clause of another SQL statement, or in the select list of a statement.

You can use subqueries to handle query requests that are expressed as the results of other queries. A statement that includes a subquery operates on rows from one table, based on its evaluation of the subquery’s select list, which can refer either to the same table as the outer query, or to a different table. In Transact-SQL, you can use a subquery almost anywhere an expression is allowed, if the subquery returns a single value. A case expression can also include a subquery.

For example, this subquery lists the names of all authors whose royalty split is more than $75:

select au_fname, au_lname
from authors 
where au_id in 
   (select au_id 
    from titleauthor 
    where royaltyper > 75)

select statements that contain one or more subqueries are sometimes called nested queries or nested select statements.

The result of a subquery that returns no values is NULL. If a subquery returns NULL, the query failed.

select price 
from titles 
where title = "Straight Talk About Computers" 

price 
------------- 
       $19.99 
 
(1 row affected)

select title, price 
from titles 
where price = $19.99 

title                                         price 
------------------------------------------    -----
The Busy Executive’s Database Guide           19.99 
Straight Talk About Computers                 19.99 
Silicon Valley Gastronomic Treats             19.99 
Prolonged Data Deprivation: Four Case Studies  19.99 

select title, price 
from titles 
where price = 
   (select price 
    from titles 
    where title = "Straight Talk About Computers") 

title                                         price
---------------------------------------       -----
The Busy Executive’s Database Guide           19.99 
Straight Talk About Computers                 19.99 
Silicon Valley Gastronomic Treats             19.99 
Prolonged Data Deprivation: Four Case Studies  19.99 

• Subquery Restrictions
  A subquery is subject to certain restrictions.
• Qualify Column Names
  Column names in a statement are implicitly qualified by tables that are referenced in the from clause at the same level.
• Subqueries with Correlation Names
  Table correlation names are required in self-joins because the table being joined to itself appears in two different roles. You can use correlation names in nested queries that refer to the same table in both an inner query and an outer query.
• Multiple Levels of Nesting
  A subquery can include one or more subqueries. You can nest up to 250 subqueries in a statement.
• Using an Asterisk in Nested select Statements
  You can use an asterisk (*) in a nested select statement that is not an exists subquery as long as the asterisk follows certain conditions.
• Subqueries in update, delete, and insert Statements
  You can nest subqueries in update, delete, and insert statements as well as in select statements.
• Subqueries in Conditional Statements
  You can use subqueries in conditional statements.
• Subqueries Instead of Expressions
  In Transact-SQL, you can substitute a subquery almost anywhere you can use an expression in a select, update, insert, or delete statement.
• Types of Subqueries
  There are two basic types of subqueries: expression and quantified predicate.
• Correlated Subqueries
  In queries that include a repeating subquery, or correlated subquery, the subquery depends on the outer query for its values. The subquery is executed repeatedly, once for each row that is selected by the outer query.