Summary: in this tutorial, you will learn about the SQL correlated subquery, which is a subquery that depends on the outer query.
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:
Code language: PHP (php)
SELECT companyname, city, (SELECT SUM(unitprice * quantity) FROM orders INNER JOIN orderdetails ON orderdetails.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 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 the
WHERE clause to find customers that have total sales more than 100K:
Code language: HTML, XML (xml)
SELECT companyname, city FROM customers WHERE 100000 < ( SELECT SUM(unitprice * quantity) FROM orders INNER JOIN orderdetails ON orderdetails.orderid = orders.orderid WHERE orders.customerid = customers.customerid);
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:
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)
In the above query:
- The subquery calculates the average unit price in each category and multiplies it with 2.
- The outer query selects the 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.