Correlated Subqueries

What is a Correlated Subquery?

A correlated subquery depends on values from the outer query. Unlike a regular subquery, which runs once for the entire query, a correlated subquery executes once for each row selected by the outer query.

This can be slower for large datasets but is very powerful.

Syntax

SELECT column1
FROM table1 outer_table
WHERE column1 operator
  (SELECT column1 FROM table2 inner_table
   WHERE inner_table.related_col = outer_table.related_col);

Example: Products Above Average Price (per Category)

Note: Our Products table in the playground is simple. Let's imagine we had a CategoryID column. We can simulate the concept.

Let's do something simpler with the existing data. Since we don't have categories, let's try to find customers where the length of their name is greater than the average length of customer names in their own country.

In this example:

1. The outer query selects a customer c1.
2. The inner query calculates the average name length for c1's country.
3. The outer query compares c1's name length to that average.