Correlated subquery execution model in SQL - Step-by-Step Execution

Concept Flow - Correlated subquery execution model

Start Outer Query Row

↓

Evaluate Correlated Subquery

↓

Use Outer Row Value in Subquery

↓

Subquery Returns Result

↓

Combine Subquery Result with Outer Row

↓

Move to Next Outer Query Row

↓

Repeat

↓

End when no more outer rows

For each row in the outer query, the correlated subquery runs using values from that row, then returns a result combined with the outer row.

Execution Sample

SQL

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Find employees whose salary is above the average salary of their own department.

Execution Table

Step	Outer Row (e.name, e.department, e.salary)	Subquery Condition	Subquery Result (AVG salary)	Outer Condition (e.salary > AVG)	Action
1	(Alice, Sales, 5000)	department = 'Sales'	4500	5000 > 4500 = True	Include Alice
2	(Bob, Sales, 4000)	department = 'Sales'	4500	4000 > 4500 = False	Exclude Bob
3	(Carol, HR, 6000)	department = 'HR'	5500	6000 > 5500 = True	Include Carol
4	(Dave, HR, 5000)	department = 'HR'	5500	5000 > 5500 = False	Exclude Dave
5	(Eve, IT, 7000)	department = 'IT'	7000	7000 > 7000 = False	Exclude Eve
6	No more rows	-	-	-	Stop execution

💡 No more outer rows to process, query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
e.name	N/A	Alice	Bob	Carol	Dave	Eve	N/A
e.department	N/A	Sales	Sales	HR	HR	IT	N/A
e.salary	N/A	5000	4000	6000	5000	7000	N/A
Subquery AVG	N/A	4500	4500	5500	5500	7000	N/A
Outer Condition Result	N/A	True	False	True	False	False	N/A

Key Moments - 3 Insights

Why does the subquery run multiple times instead of just once?

Because the subquery depends on the current outer row's department value, it must run for each outer row to get the correct average for that department (see execution_table rows 1-5).

What happens if the subquery returns no rows for a department?

The AVG function returns NULL, so the outer condition comparing salary > NULL becomes false, excluding that outer row (not shown in this example but important to know).

Why is Eve excluded even though her salary equals the average?

The condition uses > (greater than), so equality does not satisfy it. Eve's salary equals the average, so the condition is false (see execution_table row 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the subquery AVG salary result when processing Carol's row?

A 4500

B 6000

C 5500

D 7000

💡 Hint

Check execution_table row 3 under 'Subquery Result (AVG salary)'.

At which step does the outer condition become false for the first time?

A Step 2

B Step 3

C Step 1

D Step 5

💡 Hint

Look at 'Outer Condition Result' column in variable_tracker after each step.

If the condition changed to >= instead of >, which employee would be included additionally?

A Dave

B Eve

C Bob

D No change

💡 Hint

Check execution_table row 5 where Eve's salary equals the average but was excluded due to > condition.

Concept Snapshot

Correlated Subquery:
- Runs once per outer query row
- Uses outer row values inside subquery
- Returns result combined with outer row
- Useful for row-by-row comparisons
- Can be slower due to repeated subquery execution

Full Transcript

A correlated subquery runs once for each row of the outer query. It uses values from the current outer row inside the subquery condition. For example, to find employees earning more than their department's average salary, the subquery calculates the average salary for the department of the current employee. This means the subquery runs multiple times, once per employee. The outer query then compares the employee's salary to this average. If the condition is true, the employee is included in the result. This process repeats until all outer rows are processed. Understanding this step-by-step helps beginners see why correlated subqueries can be slower but are powerful for row-specific filtering.