Correlated subqueries execution model in PostgreSQL - Step-by-Step Execution

Concept Flow - Correlated subqueries execution model

Outer Query Row

↓

Evaluate Subquery with Outer Row Value

↓

Subquery returns result

↓

Use Subquery Result in Outer Query

↓

Next Outer Query Row

Repeat until all outer rows processed↓

Query Complete

↩Repeat until all outer rows processed

For each row in the outer query, the subquery runs using that row's values, then returns a result used by the outer query. This repeats for all outer rows.

Execution Sample

PostgreSQL

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 greater than the average salary in their own department.

Execution Table

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

💡 All outer rows processed; 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	5500	6500	7000	N/A
Subquery AVG(salary)	N/A	4500	4500	6000	6000	7000	N/A
Outer Condition Result	N/A	True	False	False	True	False	N/A

Key Moments - 3 Insights

Why does the subquery run multiple times?

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

Is the subquery result the same for all outer rows?

No, it changes depending on the outer row's department. For example, in row 1 and 2 the subquery filters by 'Sales', but in row 3 and 4 by 'HR' (execution_table column 'Subquery Condition').

What happens if the outer condition is false?

The outer row is excluded from the final result, as shown in rows 2, 3, and 5 where the condition is false and the action is 'Exclude'.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result when processing Dave's row?

A 4500

B 6000

C 7000

D 6500

💡 Hint

Check the row where Outer Row is Dave (row 4) and see the Subquery Result column.

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

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the Outer Condition column in the execution_table and find the first 'False'.

If Eve's salary was 7500 instead of 7000, what would change in the execution_table?

A Eve's department changes

B Subquery result for Eve's row changes

C Outer condition for Eve's row becomes True

D No changes

💡 Hint

Compare Eve's salary with the subquery AVG salary in the last row of execution_table.

Concept Snapshot

Correlated subqueries run once per outer row using that row's values.
The subquery result depends on the outer row.
Outer query uses subquery result to filter or compute.
This repeats for all outer rows.
Useful for comparisons within groups or related data.

Full Transcript

In a correlated subquery, the database processes each row of the outer query one by one. For each outer row, it runs the subquery using values from that row. The subquery returns a result, such as an average salary for the employee's department. The outer query then uses this result to decide if the row meets the condition. This process repeats until all outer rows are checked. For example, employees with salaries above their department's average are selected. The subquery runs multiple times, once per outer row, because it depends on the outer row's data. If the condition is false, the row is excluded. This step-by-step execution ensures accurate filtering based on related data.