Curriculum
In this tutorial, you will learn about the SQL Server subquery and how to use the subquery for querying data.
A subquery is a query nested inside another statement such as SELECT,Â
INSERT,Â
UPDATE, orÂ
DELETE.
Let’s see the following example.
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 located inÂ
New York
:
SELECT order_id, order_date, customer_id FROM sales.orders WHERE customer_id IN ( SELECT customer_id FROM sales.customers WHERE city = 'New York' ) ORDER BY order_date DESC;
Here is the result:
In this example, the following statement is a subquery:
SELECT customer_id FROM sales.customers WHERE city = 'New York'
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 located in New York
.
SELECT customer_id FROM sales.customers WHERE city = '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. 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.
A subquery can be nested within another subquery. SQL Server supports up to 32 levels of nesting. Consider the following example:
SELECT product_name, list_price FROM production.products WHERE list_price > ( SELECT AVG (list_price) FROM production.products WHERE brand_id IN ( SELECT brand_id FROM production.brands WHERE brand_name = 'Strider' OR brand_name = 'Trek' ) ) ORDER BY list_price;
First, SQL Server executes the following subquery to get a list of brand identification numbers of the Strider
 and Trek
 brands:
SELECT brand_id FROM production.brands WHERE brand_name = 'Strider' OR brand_name = 'Trek';
Second, SQL Server calculates the average price list of all products that belong to those brands.
SELECT AVG (list_price) FROM production.products WHERE brand_id IN (6,9)
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.
You can use a subquery in many places:
INÂ orÂ
NOT IN
ANYÂ orÂ
ALL
EXISTS or NOT EXISTS
UPDATE,Â
DELETE, or
INSERTÂ statementFROM
 clauseIf 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.
SELECT order_id, order_date, ( SELECT MAX (list_price) FROM sales.order_items i WHERE i.order_id = o.order_id ) AS max_list_price FROM sales.orders o order by order_date desc;
IN
 operatorA 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.
SELECT product_id, product_name FROM production.products WHERE category_id IN ( SELECT category_id FROM production.categories WHERE category_name = 'Mountain Bikes' OR category_name = 'Road Bikes' );
This query is evaluated in two steps:
Mountain Bikes
 and code
 Road Bikes.ANY
 operatorThe subquery is introduced with the ANY
 operator has the following syntax:
scalar_expression comparison_operator ANY (subquery)
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.
SELECT product_name, list_price FROM production.products WHERE list_price >= ANY ( SELECT AVG (list_price) FROM production.products GROUP BY brand_id )
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.
ALL
 operatorThe ALL operator has the same syntax as theÂ
ANYÂ operator:
scalar_expression comparison_operator ALL (subquery)
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:
SELECT product_name, list_price FROM production.products WHERE list_price >= ALL ( SELECT AVG (list_price) FROM production.products GROUP BY brand_id )
EXISTS
 or NOT EXISTS
The following illustrates the syntax of a subquery introduced with EXISTS operator:
WHERE [NOT] EXISTS (subquery)
The EXISTS
 operator returns TRUE
 if the subquery return results; otherwise, it returns FALSE
.
The NOT EXISTS
 negates the EXISTS
 operator.
The following query finds the customers who bought products in 2017:
SELECT customer_id, first_name, last_name, city FROM sales.customers c WHERE EXISTS ( SELECT customer_id FROM sales.orders o WHERE o.customer_id = c.customer_id AND YEAR (order_date) = 2017 ) ORDER BY first_name, last_name;
If you use the NOT EXISTS
 instead of EXISTS
, you can find the customers who did not buy any products in 2017.
SELECT customer_id, first_name, last_name, city FROM sales.customers c WHERE NOT EXISTS ( SELECT customer_id FROM sales.orders o WHERE o.customer_id = c.customer_id AND YEAR (order_date) = 2017 ) ORDER BY first_name, last_name;
FROM
 clauseSuppose 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 staff:
SELECT staff_id, COUNT(order_id) order_count FROM sales.orders GROUP BY staff_id;
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:
SELECT AVG(order_count) average_order_count_by_staff FROM ( SELECT staff_id, COUNT(order_id) order_count FROM sales.orders GROUP BY staff_id ) t;
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:
FROM
 clause.