SQL Correlated Subquery

Summary: in this tutorial, you will learn about the SQL correlated subquery, which is a subquery that depends on the outer query.

This tutorial requires a good knowledge of subquery. If you don’t know anything about the subquery, check it out the subquery tutorial before moving forward with this tutorial.

Introduction to SQL correlated subquery

A correlated subquery is a subquery that depends on the outer query. It means that the WHERE clause of the correlated subquery uses the data of the outer query.

The main difference between a correlated subquery and a non-correlated subquery is that you cannot execute a correlated subquery alone like a non-correlated subquery. In addition, a correlated subquery executes once for each selected row from the outer query.

A correlated subquery is also known as repeating subquery or synchronized subquery.

SQL correlated subquery examples

Let’s take a look at some examples to understand the idea of the correlated subquery.

SQL correlated subquery in the SELECT clause example

The following query selects top five customers by sales:

Top five customers by revenue

The correlated subquery calculates total sales for each selected customer from the customers table. The selected customerid from the outer query is passed to the correlated subquery for getting the corresponding sales data.

SQL correlated subquery in WHERE clause example

You can also use the correlated subquery in a WHERE clause. For example, the following example uses a correlated subquery in WHERE clause to find customers that have total sales more than 100K:

sql correlated subquery in where clause example

For each customer, the correlated subquery calculates the total sales. The WHERE clause checks if the total sales, which is returned by the correlated subquery, is greater than 100K.

SQL correlated subquery in HAVING clause example

You can use a correlated subquery in the HAVING clause of an outer query. See the following example:

SQL correlated subquery with HAVING example

In the above query:

  • The subquery calculate the average unit price in each category and multiply it with 2.
  • The outer query selects category of the product whose unit price is greater than the double average unit price returned by the correlated subquery.

In this tutorial, you have learned about SQL correlated subquery. We hope you now have a better understanding of correlated subquery and apply it effectively in your work.