Think of a correlated subquery like asking a personalized question for each person. Instead of asking "What's the company average salary?" once, you're asking "What's the average salary in THIS person's department?" for every single employee. The answer changes based on who you're asking about.

The magic word is "correlated" - it means the inner query is connected to (correlated with) the outer query. The subquery looks at values from the current row being processed and uses them in its calculation. It's like having a conversation where each answer depends on the specific person you're talking about.

Here's the catch: if you have 1,000 employees, that subquery runs 1,000 times. Each employee gets their own personalized calculation. This makes correlated subqueries powerful for row-specific logic, but potentially slow if you're not careful with indexes.

**What Makes a Subquery "Correlated"?**

A subquery becomes correlated when it references columns from the outer query. Look for the connection:

Non-correlated (independent):

SELECT name, salary FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery doesn't reference outer query

-- Runs once, returns one value (e.g., 75000)

-- Fast!

Correlated (dependent):

SELECT e1.name, e1.salary FROM employees e1

WHERE e1.salary > (

SELECT AVG(e2.salary) FROM employees e2

WHERE e2.department = e1.department -- References e1 from outer query!

);

-- Subquery references e1.department

-- Runs once per employee

-- Slower, but gives per-department comparison

The key is that reference: e2.department = e1.department. That's what makes it correlated.

**How Execution Works - Step by Step:**

Let's trace through an example with 3 employees:

Query:

SELECT e1.name, e1.department, e1.salary

FROM employees e1

WHERE e1.salary > (

SELECT AVG(e2.salary) FROM employees e2

WHERE e2.department = e1.department

);

Execution:

1. Outer query starts with first employee: Alice, Engineering, $95,000

- Subquery executes: AVG(salary) WHERE department = 'Engineering'

- Returns: $90,000

- Comparison: $95,000 > $90,000? Yes

- Include Alice in results

2. Outer query moves to second employee: Bob, Engineering, $85,000

- Subquery executes AGAIN: AVG(salary) WHERE department = 'Engineering'

- Returns: $90,000 (same department as Alice)

- Comparison: $85,000 > $90,000? No