Subquery Fundamentals

A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.

SELECT Ord.OrderID, Ord.OrderDate,

       (SELECT MAX(OrdDet.UnitPrice)

        FROM Northwind.dbo.[Order Details] AS OrdDet

        WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice

FROM Northwind.dbo.Orders AS Ord

  

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference 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 yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. This is an example showing both a subquery SELECT and a join SELECT that return the same result set:

/* SELECT statement built using a subquery. */

SELECT ProductName

FROM Northwind.dbo.Products

WHERE UnitPrice =

      (SELECT UnitPrice

       FROM Northwind.dbo.Products

       WHERE ProductName = 'Sir Rodney''s Scones')

  

/* SELECT statement built using a join that returns

   the same result set. */

SELECT Prd1.ProductName

FROM Northwind.dbo.Products AS Prd1

     JOIN Northwind.dbo.Products AS Prd2

       ON (Prd1.UnitPrice = Prd2.UnitPrice)

WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

  

A subquery nested in the outer SELECT statement has the following components:

The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Statements that include a subquery usually take one of these formats:

In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft® SQL Server™ actually processes Transact-SQL statements with subqueries).

There are three basic types of subqueries, those that:

See Also

SELECT

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.