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:

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;Code language: PHP (php)

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:

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);Code language: HTML, XML (xml)

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.