Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Subqueries (SQL Server)
In this articleApplies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)A subquery is a query that is nested inside a 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
Subquery fundamentalsA 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's usually no performance difference between a statement that includes a subquery and a semantically equivalent version that doesn't. For architectural information on how SQL Server processes queries, see SQL statement processing. 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. The following example shows both a subquery
A subquery nested in the outer SELECT statement has the following components:
The SELECT query of a subquery is always enclosed in parentheses. It can't include a A subquery can be nested inside the If a table appears only in a subquery and not in the outer query, then columns from that table can't 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 isn't necessarily how SQL Server actually processes Transact-SQL statements with subqueries). There are three basic types of subqueries. Those that:
Subquery rulesA subquery is subject to the following restrictions:
Qualify column names in subqueriesIn the following example, the BusinessEntityID column in the
The general
rule is that column names in a statement are implicitly qualified by the table referenced in the Here's what the query looks like with these implicit assumptions specified:
It's never wrong to state the table name explicitly, and it's always possible to override implicit assumptions about table names with explicit qualifications. Important If a column is referenced in a subquery that does not exist in the table referenced by the subquery's Multiple levels of nestingA subquery can itself include one or more subqueries. Any number of subqueries can be nested in a statement. The following query finds the names of employees who are also sales persons.
Here is the result set.
The innermost query returns the sales person IDs. The query at the next higher level is evaluated with these sales person IDs and returns the contact ID numbers of the employees. Finally, the outer query uses the contact IDs to find the names of the employees. You can also express this query as a join:
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the
Here is the result set.
The previous subquery in this statement can't be evaluated independently of the outer query. It needs a value for Employee.BusinessEntityID, but this value changes as SQL Server examines different rows in Employee. That is
exactly how this query is evaluated: SQL Server considers each row of the Employee table for inclusion in the results by substituting the value in each row into the inner query. For example, if SQL Server first examines the row for
The result is 0.00
(
Because this is false, the row for Correlated subqueries can also include table-valued functions in the Subquery typesSubqueries can be specified in many places:
Subqueries with table aliasesMany statements in which the subquery and the outer query refer to the same table can be stated as self-joins (joining a table to itself). For example, you can find addresses of employees from a particular state using a subquery:
Here is the result set.
Or you can use a self-join:
Table aliases
Explicit table aliases make it clear that a reference to Person.Address in the subquery doesn't mean the same thing as the reference in the outer query. Subqueries with INThe result of a subquery introduced with
Here is the result set.
This statement is evaluated in two steps. First, the inner query returns the subcategory identification number that matches the name 'Wheel' (17). Second, this value is substituted into the outer query, which finds the product names that go with the subcategory identification numbers in
One difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result. For example, if you want to include the name of the product subcategory in the result, you must use a join version.
Here is the result set.
The following query finds the name of all vendors whose credit rating is good, from whom Adventure Works Cycles orders at least 20 items, and whose average lead time to deliver is less than 16 days.
Here is the result set.
The inner query is
evaluated, producing the ID numbers of the vendors who meet the subquery qualifications. The outer query is then evaluated. Notice that you can include more than one condition in the Using a join, the same query is expressed like this:
A join can always be expressed as a subquery. A subquery can often, but not always, be expressed as a join. This is because joins are symmetric: you can join table A to B in either order and get the same answer. The same isn't true if a subquery is involved. Subqueries with NOT INSubqueries introduced with the keyword
This statement can't be converted to a join. The analogous not-equal join has a different meaning: It finds the names of products that are in some subcategory that isn't a finished bicycle. Subqueries in UPDATE, DELETE, and INSERT statementsSubqueries can be nested in the The following example doubles the value in the ListPrice column in the Production.Product table. The subquery in the
Here is an equivalent
For clarity in case the same table is itself referenced in other subqueries, use the target table's alias:
Subqueries with comparison operatorsSubqueries can be introduced with one of the comparison operators ( A subquery introduced with an unmodified
comparison operator (a comparison operator not followed by To use a subquery introduced with an unmodified comparison operator, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value. For example, if
you assume each sales person only covers one sales territory, and you want to find the customers located in the territory covered by Linda Mitchell, you can write a statement with a subquery introduced with the simple
If, however, Subqueries introduced with unmodified comparison operators often include aggregate functions, because these return a single value. For example, the following statement finds the names of all products whose list price is greater than the average list price.
Because subqueries introduced with unmodified comparison operators must return a single value, they can't include
Comparison operators modified by ANY, SOME, or ALLComparison operators that introduce a subquery can be modified by the keywords Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a Using the > comparison operator as an example, For a row in a subquery with Similarly, The following query provides an example of a subquery introduced with a comparison operator modified by
For each Product subcategory, the inner query finds the maximum list
price. The outer query looks at all of these values and determines which individual product's list prices are greater than or equal to any product subcategory's maximum list price. If If the subquery doesn't return any values, the entire query fails to return any values. The
Here's the result set for either query:
The
For example, the following query finds customers located in a territory not covered by any sales persons.
The results include all customers, except those whose sales territories are NULL, because every territory that is assigned to a customer is covered by a sales person. The inner query finds all the sales territories covered by sales persons, and then, for each territory, the outer query finds the customers who aren't in one. For the same reason, when you use You can get the same results with the Subqueries with EXISTSWhen a subquery is introduced with the keyword A subquery introduced with EXISTS has the following syntax:
The following query finds the names of all products that are in the Wheels subcategory:
Here is the result set.
To understand the results of this query, consider the name of each product in turn. Does this value cause the subquery to return at least one row? In other words, does the query cause the existence test to evaluate to Notice that subqueries that are introduced with EXISTS are a bit different from other subqueries in the following ways:
The For example, the preceding query can be expressed by using
Subqueries with NOT EXISTS
For example, to find the names of products that aren't in the wheels subcategory:
Subqueries used in place of an expressionIn Transact-SQL, a subquery can be substituted anywhere an expression can be used in The following example illustrates how you might use this enhancement. This query finds the prices of all mountain bike products, their average price, and the difference between the price of each mountain bike and the average price.
See alsoSyntax
Query performance concepts
FeedbackSubmit and view feedback for What are the maximum number of columns that can be returned when used with order by?There is no limit to the number of columns in the ORDER BY clause; however, the total size of the columns specified in an ORDER BY clause cannot exceed 8,060 bytes.
In which of the following statements where clause is not allowed?Answer: C. A column alias cannot be used in WHERE clause conditions but can be used in SELECT statement and ORDER BY clause.
Which of the following would you do to add drop or change the options in the same PROC SQL step?Resetting PROC SQL Options with the RESET Statement
Use the RESET statement to add, drop, or change the options in the PROC SQL statement. You can list the options in any order in the PROC SQL and RESET statements.
In which of the following queries can you replace the SAS summary function mean with the ANSI summary function AVG and get the same results?In which of the following queries can you replace the SAS summary function MEAN with the ANSI summary function AVG and get the same results? You can specify columns created by summary functions in the HAVING clause, with or without the CALCULATED keyword.
|