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.
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.
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.
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
Table: customers
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
Table: orders
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 13th 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 13th June where the order value is more than 3297.25 (i.e. 3550 and 3500). 2 other orders of 13th 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.