Lessons › SQL › Deep Dive: Advanced Joins & Subqueries

SQL lesson

Correlated subqueries

A correlated subquery is a mini-query nested inside a larger one that reruns for every single row, using a value from that row to shape its own result.

Example

Comparing Each Retail Order with a Correlated Subquery

SELECT order_id, customer_id, revenue
FROM orders o
WHERE revenue > (
  SELECT AVG(revenue)
  FROM orders i
  WHERE i.customer_id = o.customer_id
)

Result:

Step by step

1. Run the query without AVG so you can see what the unfiltered values look like.

   SELECT order_id, customer_id, revenue
   FROM orders o
   WHERE revenue > (
     SELECT revenue
     FROM orders i
     WHERE i.customer_id = o.customer_id
   );

2. AVG returns the mean of the values in a column.

   SELECT order_id, customer_id, revenue
   FROM orders o
   WHERE revenue > (
     SELECT AVG(revenue)
     FROM orders i
     WHERE i.customer_id = o.customer_id
   );

3. AS gives the AVG result a friendly column name so the output reads cleanly.

   SELECT order_id, customer_id, revenue
   FROM orders o
   WHERE revenue > (
     SELECT AVG(revenue) AS avg_result
     FROM orders i
     WHERE i.customer_id = o.customer_id
   );

Practice

Try each one in your head first, then open the answer.

SELECT * FROM orders t1 WHERE revenue > (SELECT AVG(revenue) FROM orders t2 WHERE t2.category = t1.category)

SELECT * FROM customers t1 WHERE email > (SELECT AVG(email) FROM customers t2 WHERE t2.phone = t1.phone)

SELECT * FROM products t1 WHERE price > (SELECT AVG(price) FROM products t2 WHERE t2.brand = t1.brand)

SELECT * FROM store_sales t1 WHERE revenue > (SELECT AVG(revenue) FROM store_sales t2 WHERE t2.store_id = t1.store_id)

SELECT * FROM employees t1 WHERE salary > (SELECT AVG(salary) FROM employees t2 WHERE t2.full_name = t1.full_name)