Problem solve Get help with specific problems with your technologies, process and projects.

T-SQL subquery basics: When and how to apply them in SQL Server

Subqueries in T-SQL give admins flexibility when it comes to analyzing the inner details of subsets of data. Learn how to apply subqueries with this comprehensive rundown.

Past articles on Microsoft Transact-SQL have examined T-SQL mathematical functions, including algebraic and numeric functions. Part one of this short series moves on to analyze the basics of T-SQL subqueries and how to use them as expressions. Part two reviews subqueries in relation to comparison operators, the IN and NOT IN operators, and the EXIST and NOT EXIST operators.

One of the 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:

  FirstName + ' ' + LastName AS FullName,
  ROUND(SalesYTD, 2) AS SalesYTD,
    FROM Sales.vSalesPerson
    WHERE JobTitle = 'Sales Representative'
  ) AS AvgSales
  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 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 Name
    FROM Production.ProductSubcategory
    WHERE  ProductSubcategoryID = Production.Product.
  ) 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)
   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:

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

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

Dig Deeper on SQL-Transact SQL (T-SQL)