Understanding Correlated Subquery

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

If you want to know what a subquery is, check out the subquery tutorial before going forward with this tutorial.

Introduction to correlated subquery

A correlated subquery is a subquery that depends on the outer query. It means the WHERE clause of the correlated subquery uses result from the outer query. The main difference between a correlated subquery and a non-correlated subquery is that you cannot execute a correlated subquery independently like a non-correlated subquery. More importantly, 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.

Correlated subquery examples

Let’s take a look at several examples to understand the basic idea behind the correlated subquery.

Correlated subquery in SELECT clause

In the example below, we are going to find top five customers based on total sales.

SELECT companyname,
city,
(SELECT sum(unitprice * quantity)
FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE orders.customerid = customers.customerid
) AS total
FROM customers
ORDER BY total DESC
LIMIT 5

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 corresponding sales data.

Correlated subquery in WHERE clause

We can use correlated subquery in WHERE clause as well. The following is an example of using a correlated subquery in WHERE clause to find customers that have total sales more than 100K:

SELECT companyname,
       city
FROM customers
WHERE 100000 <
( SELECT sum(unitprice * quantity)
  FROM orders
  INNER JOIN order_details ON order_details.orderid = orders.orderid
  WHERE orders.customerid = customers.customerid
)

For each customer, the correlated subquery calculates total sales based on the orders. In the WHERE clause, if the total sales returns from the correlated subquery is greater than 100K, the query will return that customer.

Correlated Subquery in HAVING clause

You can use a correlated subquery in the HAVING clause of an outer query. For example, to find the product category for which the maximum unit price is greater than twice the average unit price for the category, you use the following query:

SELECT t1.categoryID, categoryName
FROM products t1
INNER JOIN categories c ON c.categoryID = t1.categoryID
GROUP BY categoryID
HAVING MAX(unitprice) > ALL(
	SELECT 2 * AVG(unitprice)
	FROM products t2
	WHERE t1.categoryID = t2.categoryID
)

The correlated subquery executes once for each group returns from the outer query, that is once for each product category.

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