A subquery is a query nested inside another statement such as
SELECT
, INSERT
, UPDATE
, or DELETE
.
Let’s see the following example.
Consider the
orders
and customers
tables from the sample database.
The following statement shows how to use a subquery in the
WHERE
clause of a SELECT
statement to find the sales orders of the customers who locate in New York
:
Here is the result:

In this example, the following statement is a subquery:
Note that you must always enclose the
SELECT
query of a subquery in parentheses ()
.
A subquery is also known as an inner query or inner select while the statement containing the subquery is called an outer select or outer query:

SQL Server executes the whole query example above as follows:
First, it executes the subquery to get a list of customer identification numbers of the customers who locate in
New York
.
Second, SQL Server substitutes customer identification numbers returned by the subquery in the
IN
operator and executes the outer query to get the final result set.
As you can see, by using the subquery, you can combine two steps together. The subquery removes the need for selecting the customer identification numbers and plugging them into the outer query. Moreover, the query itself automatically adjusts whenever the customer data changes.
Nesting subquery
A subquery can be nested within another subquery. SQL Server supports up to 32 levels of nesting. Consider the following example:

First, SQL Server executes the following subquery to get a list of brand identification numbers of the
Strider
and Trek
brands:
Second, SQL Server calculates the average price list of all products that belong to those brands.
Third, SQL Server finds the products whose list price is greater than the average list price of all products with the
Strider
or Trek
brand.SQL Server subquery types
You can use a subquery in many places:
- In place of an expression
- With
IN
orNOT IN
- With
ANY
orALL
- With
EXISTS
orNOT EXISTS
- In
UPDATE
,DELETE
, orINSERT
statement - In the
FROM
clause
SQL Server subquery is used in place of an expression
If a subquery returns a single value, it can be used anywhere an expression is used.
In the following example, a subquery is used as a column expression named
max_list_price
in a SELECT
statement.
SQL Server subquery is used with IN
operator
A subquery that is used with the
IN
operator returns a set of zero or more values. After the subquery returns values, the outer query makes use of them.
The following query finds the names of all mountain bikes and road bikes products that the Bike Stores sell.

This query is evaluated in two steps:
- First, the inner query returns a list of category identification numbers that match the names
Mountain Bikes
andcode
Road Bikes. - Second, these values are substituted into the outer query that finds the product names which have the category identification number match with one of the values in the list.
SQL Server subquery is used with ANY
operator
The subquery is introduced with the
ANY
operator has the following syntax:
Assuming that the subquery returns a list of value v1, v2, … vn. The
ANY
operator returns TRUE
if one of a comparison pair (scalar_expression
, vi) evaluates to TRUE
; otherwise, it returns FALSE
.
For example, the following query finds the products whose list prices are greater than or equal to the average list price of any product brand.

For each brand, the subquery finds the maximum list price. The outer query uses these max prices and determines which individual product’s list price is greater than or equal to any brand’s maximum list price.
SQL Server subquery is used with ALL
operator
The
ALL
operator returns TRUE
if all comparison pairs (scalar_expression
, vi) evaluate to TRUE
; otherwise, it returns FALSE
.
The following query finds the products whose list price is greater than or equal to the average list price returned by the subquery:

SQL Server subquery is used with EXISTS
or NOT EXISTS
The following illustrates the syntax of a subquery introduced with
EXISTS
operator:
The
EXISTS
operator returns TRUE
if the subquery return results; otherwise it returns FALSE
.
On the other hand, the
NOT EXISTS
is opposite to the EXISTS
operator.
The following query finds the customers who bought products in 2017:

If you use the
NOT EXISTS
instead of EXISTS
, you can find the customers who did not buy any products in 2017.
SQL Server subquery in the FROM
clause
Suppose that you want to find the average of the sum of orders of all sales staff. To do this, you can first find the number of orders by staffs:

Then, you can apply the
AVG()
function to this result set. Since a query returns a result set that looks like a virtual table, you can place the whole query in the FROM
clause of another query like this:
The query that you place in the
FROM
clause must have a table alias. In this example, we used the t as the table alias for the subquery. To come up with the final result SQL Server carries the following steps:- Execute the subquery in the
FROM
clause. - Use the result of the subquery and execute the outer query.
In this tutorial, you have learned about the SQL Server subquery concept and how to use various subquery types to query data.
0 Comments