SQL Server SUBQUERY

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use a SUBQUERY in a SQL statement to expand the scope of operation of the statement and to narrow its focus with respect to the output and get more specific output.

What is SUBQUERY in SQL Server?

A SUBQUERY is a SQL query within a SQL statement. A subquery can be part of a SELECT, INSERT, UPDATE or DELETE statement and is itself always a SELECT query. It is also possible for a subquery to have another subquery within it. A subquery within a subquery is called a NESTED SUBQUERY and the phenomenon is called NESTING. SQL Server supports 32 levels of nesting i.e. 32 subqueries in a statement. A SUBQUERY is also called an INNER QUERY or INNER SELECT and the main SQL statement of which it is a part and which it feeds with data is called the OUTER QUERY or MAIN QUERY.

Operation

A subquery must be placed within brackets or parenthesis in a SQL clause. Depending on the purpose and the placement of the subquery in a SQL statement we can identify 3 types of subqueries. They are the following.

1. Subquery in SELECT clause – Here the subquery is one of the expressions in the select list. Usually in such cases a subquery is used with an aggregate function to fetch a value which forms part of the resultset.
2. Subquery in FROM clause – A subquery when used in the from clause generates a resultset which acts as a table and from which columns can be added to the final resultset or combined with columns from another table through a JOIN in the final resultset. That is why a SELECT SUBQUERY in the FROM clause is also called a derived table or inline view (as it creates a temporary inline table to pick columns from).  It is rarely used because of its complexity.
3. Subquery in WHERE clause – This is the most common usage where a subquery is part of the where clause and returns a value or set of values for the outer query to work with. These are also called nested subqueries.

SQL Server SUBQUERY Syntax

The basic syntax of the above-mentioned subquery types is given below.

SELECT clause subquery
SELECT select_list, (subquery) FROM table_name;

WHERE clause subquery
SELECT select_list FROM table_name WHERE (subquery);

FROM clause subquery
SELECT select_list FROM (subquery);

In this syntax,

• select_list – the list of expressions (usually table columns).
• subquery – the SELECT query within the main query which provides input to the main query for further processing.
• table_name – the table on which the main query or statement operates.

SQL Server SUBQUERY Examples

Let us understand the different subquery types with the help of some examples. Suppose we have two tables in the company database, one called customers table holding the information about customers and another called orders table holding the information of orders placed by the customers at different points in time. We will analyze these tables through SQL queries with the help of appropriate subqueries. The tables are represented below.

cust_id	cust_name	cust_city	cust_contact
101	Herbert Jones	Delaware	16633775159
102	Nancy Powell	Philadelphia	16277212992
103	Daniel Mattis	Delaware	16107575525

order_id	cust_id	order_date	order_value
210	103	6/11/2019	5500
211	103	6/11/2019	5100
212	101	6/13/2019	3139
213	102	6/13/2019	3000
214	101	6/13/2019	3550
215	101	6/13/2019	3500
216	103	6/22/2019	5000
217	102	6/22/2019	5500
218	102	6/22/2019	3000

1) SQL Server SUBQUERY –  WHERE subquery example

We will begin with the WHERE subquery which is the simplest of all the subquery types. Suppose we want to find out the number of orders received from the city of Philadelphia. We can issue the below statement with a subquery in the WHERE clause to retrieve the required information.

SELECT order_id, order_date, order_value
FROM orders
WHERE cust_id IN
(SELECT cust_id FROM customers WHERE cust_city='Philadelphia')
ORDER BY order_value DESC;

It will generate the required following result showing all orders received from the city of Philadelphia with their order id’s and dates.

order_id	order_date	order_value
217	6/22/2019	5500
218	6/22/2019	3000
213	6/13/2019	3000

Explanation: Here first the subquery will execute and return the list of cust_ids for the city of Philadelphia and then the outer main query will execute and display the corresponding information from the orders table.

2)  SQL Server SUBQUERY –  SELECT subquery example

Now suppose we want to find the average order value for each of the customers with the customer name. We can do so with the help of the following statement with a subquery in the SELECT list to fetch the average order value from the orders table.

SELECT c.cust_name,
(SELECT avg(order_value) FROM orders o WHERE o.cust_id = c.cust_id) av_order_value
FROM customers c;

It will generate the required following result listing the customer names against their order average.

cust_name	av_order_value
Herbert Jones	3396
Nancy Powell	3833
Daniel Mattis	5200

Explanation: Here we have the subquery as one of the expressions in the SELECT list. First the subquery compares the cust_id from both tables (orders and customers) to get the list of cust_id’s which match (which is basically all the cust_id’s) with the help of the WHERE clause. Then it calculates the average of the order value for each customer from the orders table with the help of the average aggregate function. Finally, the outer main query patches the average order values with the customer names retrieved from the customers table to generate the final resultset. Note that c and o are aliases for customers and orders table respectively.

3)  SQL Server SUBQUERY –  FROM subquery example

Now we will check out an example of the FROM subquery. Suppose we want to find out the total value of the orders placed by the different customers. We can do so with the help of the following statement which uses a subquery with the FROM clause whose resultset acts like a table for the main outer query.

SELECT c.cust_id, c.cust_name, o.tot_val
FROM
(SELECT cust_id, sum(order_value) tot_val
FROM orders group by cust_id) o
INNER JOIN customers c ON o.cust_id=c.cust_id;

It will generate the required following result listing the customers against total order values.

cust_id	cust_name	tot_val
101	Herbert Jones	10189
102	Nancy Powell	11500
103	Daniel Mattis	15600

Explanation: Here the subquery in the FROM clause executes first and generates the list of cust_id’s and the total order value for each cust_id (i.e. customer) with the help of the SUM aggregate function. This resultset then becomes one of the tables for the main query. The main query then creates a JOIN of the subquery generated table (aliased as o) with the customers table on the common cust_id column and produces the final result fetching cust_id and cust_name from the customers table and tot_val (i.e. total order value) from the subquery generated table.

4) SQL Server SUBQUERY –  Nested subquery example

Now let us see an example of nested subquery where a subquery contains another subquery within it and the output of the nested subquery is used by the parent query for further processing and whose output is used by the main outer query for final processing. Suppose we want to find out the order details of orders whose order value is more than the average order value of orders received on a particular date (say 2019-06-13 i.e. 13 June 2019). We can do so with the following statement which contains two subqueries in it one nested within the other. 

SELECT order_id, order_date, order_value
FROM orders
WHERE order_value > (SELECT avg(order_value) FROM orders
WHERE order_date in
(SELECT order_date FROM orders WHERE order_date IN ('2019-06-13')));

It will generate the required following result.

order_id	order_date	order_value
210	6/11/2019	5500
211	6/11/2019	5100
214	6/13/2019	3550
215	6/13/2019	3500
216	6/22/2019	5000
217	6/22/2019	5500

Explanation: Here first the innermost subquery executes and retrieves the orders placed on the 13^th of June. Then the second level subquery calculates the average order value of those orders with the help of the average aggregate function. The average is 3297.25 (i.e. 3139+3000+3550+3500/4). Then the main query checks the order value of every order to see if it is more than 3297.25. If it is then those orders form the final resultset which is above. Note that the resultset also includes 2 order from 13^th June where the order value is more than 3297.25 (i.e. 3550 and 3500). 2 other orders of 13^th June valued at 3139 and 3000 do not make it to the resultset as they are less than the order average 3297.25.

This are demo examples. In real life nested subqueries are used on large datasets for complicated data mining where multiple parameters and conditions have to be evaluated to filter and arrive at the final result and the same is done in parts with the help of subqueries and/or nested subqueries with one subquery feeding the other and narrowing down the scope till we get the distilled resultset for the purpose.