SQL Server Nested Sub Queries and Correlated Sub Queries

In this article, we will see how to write SQL Server sub queries which includes nested sub queries, multi-level sub queries and correlated sub queries. A sub query, also called as an Inner Query is a query nested with various statements like DML [INSERT/UPDATE and DELETE] and DQL [SELECT] statement.

In some cases, you can use either Sub Queries or JOINs. But there are some requirements which can be solved easily using only sub queries. When you write sub queries, you can use –

1. Nested Sub Query [Single or Multi-Level] – a query which gets executed only once and provides output to the outer query is called as sub query.

2. Correlated Sub Query –  a sub query/inner query which  gets executed multiple times for the outer query is called as correlated sub query. In correlated sub query, you take the reference of outer query column in sub query.

Nested Sub Query [Single or Multi-Level]

For demonstrating the sub queries, we will make use of Northwind database. We will consider the following tables –


We will first try out some simple sub queries. Let’s write our first sub query as shown below –


In the above query, we have to fetch all the orders for a given customer’s phone number. The relationship between the Customers and Orders table is based on CustomerID column. Hence, we are first finding the CustomerID for a given phone number and passing this CustomerID as an input to the outer query, which fetches all the orders.



In this query shown above, we are fetching all the products which are available under Category ‘Seafood’.



In the above query, we are fetching all the products which are supplied by a company called ‘Pavlova, Ltd.’.

Subquery returning more than one value

Sometimes, a sub query may return more than one value. In that situation, w cannot use the regular comparison operators as shown in this query –



The above query will throw you an exception Subquery returned more than 1 value as shown below –



When your sub query returns more than one value, then we can use some special operators for the comparison. These special operators are as listed below –

1. IN / NOT IN – This operator takes the output of inner query after inner query gets executed which can be zero or more values and send it to outer query. The outer query then fetches all the matching [IN operator] or not non matching [NOT IN operator] rows.

2. ANY – [>ANY or <ANY] – The >ANY operator takes the list of values produced by inner query and fetches all the values which are greater than the minimum value of the list. The <ANY operator takes the list of values produced by the inner query and fetches all the rows which are less than the maximum value of the list.

• For example -: >ANY(100,200,300), the ANY operator will fetch all the values greater than 100.
• For example -: <ANY(100,200,300), the ANY operator will fetch all the values lesser than 300.

3. ALL – [>ALL or <ALL] – The >ALL operator takes the list of values produced by inner query and fetches all the values which are greater than the maximum of the list. The <ALL operator takes the list of values produced by the inner query and fetches all the rows which are less than the minimum value of the list.

• For example -: >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.
• For example -: <ALL(100,200,300), the ALL operator will fetch all the values lesser than 100.

4. EXISTS – The EXISTS keyword produces a Boolean value [TRUE/FALSE]. This EXISTS checks the existence of the rows returned by the sub query.

Let’s see the queries for all the above operators as shown below –



In the above queries, the first query returns all the orders which are placed by the customers and who belong to London city. The second query is opposite to the first one which fetches all the orders placed by all the customers excluding the customers who belongs to London city.

We will take a look at second operator ANY. The queries are as shown below –



The output of ANY/SOME and ALL operators is shown below. The result is of Sub/inner query –



ANY/SOME are the same. You can make use of any one of them.

We will now take a look at EXISTS operator. The query is as shown below –



The above query will fetch all the customers if there are more than 30 orders shipped in London city.

Correlated Sub Query

In a correlated sub query, the inner query gets executed multiple times for the outer query. In correlated sub query, the inner query depends on the outer query for its values. Let’s take an example of the same –



In the above query, we are selecting all the orders where the employee’s city and order’s ship city are same.
You can also do nesting of sub queries at multi-level. For example let’s take a look at the following query –



This query selects all the order which are placed for the product Chai. The orders table is not directly connected with products table. Hence, we are first of all finding a product ID for product name ‘Chai’. Then we are finding the orderids placed for Chai product from [Order Details] table. At the end, we are then fetching the orders from the orders table.

You can use Sub Queries during INSERT, UPDATE and DELETE statements as well. Let’s take a look at UPDATE and DELETE Sub query examples as shown below –



The first query deletes all the customers whose orders shipped in London city. The second query updates all the products and sets discontinued column value false for the suppliers who belong to London city.

While working with sub queries, do remember some points as mentioned below –

• You cannot use DISTINCT keyword in sub query which contains GROUP BY clause.
• You cannot use Order By clause unless you use TOP in a sub query.
• You cannot use COMPUTE and INTO clause in sub query.

Summary – In this article we saw how to write SQL Server sub queries, simple sub query, multi-level nested sub query, correlated sub query and sub queries with DML statements.

Download the source code of this article (Github)