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.