Tip

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

Past articles on Microsoft Transact-SQL have examined T-SQL mathematical functions, including algebraic and

    Requires Free Membership to View

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:

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.

This was first published in July 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.