T-SQL language elements supported by SQL Server is the subquery, which is embedded in a SELECT, INSERT, UPDATE or DELETE statement. You can define a T-SQL subquery wherever an expression is permitted in a statement. For example, you can embed a subquery in the SELECT list of a SELECT statement, or include one in the search condition of the WHERE clause.

When you embed a T-SQL subquery into a statement, you must enclose it in parentheses. Also, the subquery cannot contain a COMPUTE or FOR BROWSE clause, and cannot contain an ORDER BY clause unless the TOP operator is included in the SELECT clause.

NOTE: A subquery is sometimes referred to as an inner select or inner query. The statement in which the subquery is embedded is sometimes referred to as an outer select or outer query.

You can often recast a statement that includes a subquery as a join. In some cases, this will improve performance, particularly if you must iterate through a subquery’s large result set for each row returned by the outer query. In a past article, I wrote more about the basics of creating joins in SQL Server.

Using T-SQL subqueries as expressions

A common way to include a subquery in a T-SQL statement is to include it as one of the column expressions in the SELECT list. You simply include the subquery as you would any other column expression. Let’s look at a few examples to demonstrate how this works.

NOTE: The examples shown in this article are based on the AdventureWorks2008 sample database, installed on a local instance of SQL Server 2008.

In the following example, I define a subquery that returns the average sales amount for sales representatives:

SELECT
  FirstName + ' ' + LastName AS FullName,
  ROUND(SalesYTD, 2) AS SalesYTD,
  (
    SELECT ROUND(AVG(SalesYTD), 2)
    FROM Sales.vSalesPerson
    WHERE JobTitle = 'Sales Representative'
  ) AS AvgSales
FROM
  Sales.vSalesPerson
WHERE
  BusinessEntityID = 275

The subquery is the third column expression in the SELECT list. Notice that it’s enclosed in parentheses. I then assigned the alias AvgSales to the column. If you were to run the subquery separately from the outer statement, it would return the value 3,054,352.75. Since the subquery is defined as one of the column expressions, however, the value is returned as a column value in the result set of the outer statement, as shown in the following results:

FullName	SalesYTD	AvgSales
Michael Blythe	4557045.05	3054352.75

In the preceding example, the subquery returned a value independent of the outer statement. In other words, you don’t need any information from the outer statement to retrieve the result you want from the subquery. You simply need to know the average sales for all sales representatives.

There are times, however, when you need the outer statement to be able to inform the subquery. In this case, use a correlated subquery, which includes a search condition that links the subquery to the outer statement. For example, in the following SELECT statement the subquery is linked to the outer statement through the ProductSubcategoryID value:

SELECT
  p.ProductID,
  p.Name,
  (
    SELECT ps.Name
    FROM Production.ProductSubcategory ps
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID
  ) AS SubcategoryName
FROM Production.Product p
WHERE p.ProductSubcategoryID IS NOT NULL
  AND p.Name LIKE '%seat%'

In this subquery, I included a WHERE clause that links the ProductSubcategoryID column in the ProductSubcategory table to the ProductSubcategoryID column in the Product table, which is called the outer statement. Notice that I used table aliases to reference the tables (p for Product and ps for ProductSubcategory). This allows me to reference the outer table in the inner query. 

Because a correlated subquery is used, the subquery returns the correct ProductSubcategory name for each row based on the ProductSubcategoryID. The following table shows the results returned by the SELECT statement:

ProductID	Name	SubcategoryName
908	LL Mountain Seat/Saddle	Saddles
909	ML Mountain Seat/Saddle	Saddles
910	HL Mountain Seat/Saddle	Saddles
911	LL Road Seat/Saddle	Saddles
912	ML Road Seat/Saddle	Saddles
913	HL Road Seat/Saddle	Saddles
914	LL Touring Seat/Saddle	Saddles
915	ML Touring Seat/Saddle	Saddles
916	HL Touring Seat/Saddle	Saddles

Since I used table aliases in the preceding example I was able to easily identify which table the ProductSubcategoryID column belongs to, but it’s not always necessary to use table aliases. By default, a subquery assumes that an unqualified column name belongs to the table specified in the subquery. If the subquery table does not include that column, the assumption is that the table belongs to the table in the outer statement.

What all this means is if a column exists in both the subquery table and the table in the outer statement, you must qualify the column name if you want it to reference the table in the outer statement, as shown in the following example:

SELECT
  ProductID,
  Name,
  (
    SELECT Name
    FROM Production.ProductSubcategory
    WHERE  ProductSubcategoryID = Production.Product.
      ProductSubcategoryID
  ) AS SubcategoryName
FROM Production.Product
WHERE ProductSubcategoryID IS NOT NULL
  AND Name LIKE '%seat%'

As you can see, I didn’t use table aliases. Instead, I qualified the second instance of the column name in the subquery so that it included the schema and table names. The statement returns the same results as the previous example.

NOTE: You may have noticed that the subquery in the first example referenced the same table as the one in the outer query. The subqueries in the two examples that followed, however, referenced a table different from the one in the outer statement. You can take either approach when adding subqueries to your statements.

The examples we’ve looked at so far use SELECT statements as the outer statements, but you can also use subqueries in INSERT, UPDATE and DELETE statements. For example, the following INSERT statement includes a subquery that determines the value to be inserted into a column:

INSERT INTOProduction.Illustration (Diagram)
VALUES
(
 (
   SELECT Diagram
   FROM Production.Illustration
   WHERE IllustrationID = 7
 )
)

The subquery returns an XML value that is then inserted into the Diagram column of the Illustration table. Notice that the subquery is enclosed in a set of parentheses that is in addition to the parentheses used to enclose all values. When you use a subquery as a value in an INSERT statement, you include it just as you would any other value expression in the statement.

You can also include a subquery in a DELETE statement. For instance, the following example uses a subquery to determine the last row inserted into the Illustration table:

DELETEProduction.Illustration
WHERE IllustrationID =
 (
SELECT MAX(IllustrationID)
FROM Production.Illustration
 )

As you can see, the subquery is part of the search condition defined by the WHERE clause. When the IllustrationID value equals the highest IllustrationID value in the table, the row is deleted.

Part two: Combining subqueries with operators in SQL Server

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.