Subquery in sql server

Introduction to SQL Server subquery

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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:

1 2 3 4 5 6

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 who locate in New York.

1 2 3 4 5 6

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 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

1 2 3 4 5 6 7

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.

1 2 3 4 5 6

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.

SQL Server subquery types

You can use a subquery in many places:

• In place of an expression
• With IN or NOT IN
• With ANY or ALL
• With EXISTS or NOT 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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;

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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:

1. First, the inner query returns a list of category identification numbers that match the names Mountain Bikes and code Road Bikes.
2. 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:

1	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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.

SQL Server subquery is used with ALL operator

The ALL operator has the same syntax as the ANY operator:

1	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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT     product_name,     list_price FROM     production.products WHERE     list_price >= ALL (         SELECT             AVG (list_price)         FROM             production.products         GROUP BY             brand_id     )

SQL Server subquery is used with EXISTS or NOT EXISTS

The following illustrates the syntax of a subquery introduced with EXISTS operator:

1	WHERE [NOT] EXISTS (subquery)

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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;

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:

1 2 3 4 5 6 7

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:

1 2 3 4 5 6 7 8 9 10 11 12

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:

• 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.