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.
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,
SELECT ROUND(AVG(SalesYTD), 2)
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:
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:
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:
|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:
WHERE ProductSubcategoryID = Production.Product.
) AS SubcategoryName
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)
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 =
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.
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.