Correlated subqueries in MySQL - Step-by-Step Execution

Concept Flow - Correlated subqueries

Start Outer Query Row

↓

Execute Subquery Using Outer Row Value

↓

Subquery Returns Result

↓

Use Subquery Result in Outer Query Condition

↓

Decide to Include Outer Row

↓

Move to Next Outer Query Row or End

For each row in the outer query, the subquery runs using that row's data, then the outer query uses the subquery result to decide if the row qualifies.

Execution Sample

MySQL

SELECT e1.name
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

Find employees whose salary is greater than the average salary in their own department.

Execution Table

Step	Outer Row (e1.name, e1.salary, e1.department)	Subquery Condition (e2.department = e1.department)	Subquery Result (AVG salary)	Outer Condition (e1.salary > AVG)	Include Row in Result
1	(Alice, 7000, Sales)	e2.department = 'Sales'	6000	7000 > 6000 = True	Yes
2	(Bob, 5000, Sales)	e2.department = 'Sales'	6000	5000 > 6000 = False	No
3	(Charlie, 8000, HR)	e2.department = 'HR'	7500	8000 > 7500 = True	Yes
4	(Diana, 7500, HR)	e2.department = 'HR'	7500	7500 > 7500 = False	No
5	(Eve, 9000, IT)	e2.department = 'IT'	8500	9000 > 8500 = True	Yes
6	No more rows	-	-	-	End

💡 All outer rows processed; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
e1.name	None	Alice	Bob	Charlie	Diana	Eve	None
e1.salary	None	7000	5000	8000	7500	9000	None
e1.department	None	Sales	Sales	HR	HR	IT	None
Subquery AVG salary	None	6000	6000	7500	7500	8500	None
Include Row?	None	Yes	No	Yes	No	Yes	None

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 (e1.department), it must run for each outer row to get the correct average salary for that department, as shown in the execution_table rows 1 to 5.

What happens if the subquery returns NULL or no rows?

If the subquery returns NULL, the comparison in the outer query (e1.salary > NULL) evaluates to false, so that outer row is excluded. This is not shown in this example but is important to remember.

How does the outer query decide to include a row?

It compares the outer row's salary to the subquery's average salary result. If the outer salary is greater, the row is included (see execution_table column 'Include Row in Result').

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery average salary when processing Diana's row?

A 6000

B 7500

C 8500

D None

💡 Hint

Check the 'Subquery Result (AVG salary)' column at step 4 in the execution_table.

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

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Outer Condition (e1.salary > AVG)' column in the execution_table and find the first 'False'.

If Bob's salary was 6500 instead of 5000, how would the 'Include Row in Result' change at step 2?

A Change to Yes

B No change, still No

C Change to NULL

D Query would error

💡 Hint

Compare Bob's new salary 6500 to the subquery average 6000 at step 2 in the execution_table.

Concept Snapshot

Correlated subqueries run once per outer row using that row's data.
Syntax: Outer query references outer row in subquery condition.
Subquery result affects outer row filtering or selection.
Useful for comparisons within groups or related data.
Runs slower than simple subqueries because of repetition.

Full Transcript

Correlated subqueries execute the subquery for each row of the outer query using that row's values. For example, to find employees earning more than their department's average salary, the subquery calculates the average salary for the current employee's department. The outer query then compares the employee's salary to this average. This process repeats for each employee row. The execution table shows each step: the outer row, the subquery condition using the outer row's department, the average salary result, the comparison, and whether the row is included. Variables like e1.name, e1.salary, and e1.department change as we move through rows. Key points include understanding why the subquery runs multiple times and how the outer query uses the subquery result to filter rows. Visual quiz questions help reinforce these ideas by referencing specific steps and values in the execution table.

Practice

1. What is a correlated subquery in MySQL?

easy

A. A subquery that uses values from the outer query for each row.

B. A subquery that runs only once before the outer query.

C. A query that joins two tables without any subqueries.

D. A subquery that returns a fixed constant value.

Solution

1. Step 1: Understand subquery behavior

   A correlated subquery depends on the outer query's current row values to execute.

2. Step 2: Compare options

   The option describing a subquery that uses values from the outer query for each row correctly defines a correlated subquery.

3. Final Answer:

   A subquery that uses values from the outer query for each row. -> Option A

4. Quick Check:

   Correlated subquery = uses outer row values [OK]

Hint: Correlated subqueries depend on outer query row values [OK]

Common Mistakes:

• Thinking subqueries always run once
• Confusing joins with subqueries
• Assuming subqueries return constants only

2. Which of the following is the correct syntax for a correlated subquery in MySQL?

easy

A. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id);

B. SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees);

C. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D. SELECT * FROM employees WHERE salary = 50000;

Solution

1. Step 1: Identify correlated subquery syntax

   A correlated subquery references a column from the outer query inside the subquery, like e2.department_id = employees.department_id.

2. Step 2: Check each option

   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); uses the outer table's column inside the subquery condition, making it a correlated subquery.

3. Final Answer:

   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); -> Option A

4. Quick Check:

   Correlated subquery syntax = SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); [OK]

Hint: Look for subquery referencing outer query table alias [OK]

Common Mistakes:

• Missing outer table reference in subquery
• Using subquery without WHERE clause for correlation
• Confusing correlated with simple subqueries

3. Given the tables:
employees(id, name, department_id, salary)
and
departments(id, name)
What will this query return?

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

medium

A. Employees whose salary is above the average salary of all departments combined.

B. Employees whose salary is above the average salary of all employees.

C. Employees whose salary is above the average salary of their department.

D. Syntax error due to subquery referencing outer query.

Solution

1. Step 1: Understand the subquery condition

   The subquery calculates the average salary for the department of the current employee (using e.department_id).

2. Step 2: Compare employee salary with department average

   The outer query selects employees whose salary is greater than their department's average salary.

3. Final Answer:

   Employees whose salary is above the average salary of their department. -> Option C

4. Quick Check:

   Correlated subquery filters by department average salary [OK]

Hint: Check if subquery filters by outer row's department_id [OK]

Common Mistakes:

• Thinking subquery calculates overall average salary
• Assuming syntax error due to correlation
• Confusing department and employee averages

4. Identify the error in this correlated subquery:

SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department_id);

medium

A. Subquery cannot reference outer query columns.

B. Alias 'e1' is not allowed in subquery.

C. Missing GROUP BY clause in subquery.

D. Column 'department' does not exist in employees table.

Solution

1. Step 1: Check column names in subquery condition

   The subquery compares e2.department to e1.department_id, but 'department' column does not exist in employees table.

2. Step 2: Confirm correct column usage

   The correct column should be e2.department_id to match e1.department_id for correlation.

3. Final Answer:

   Column 'department' does not exist in employees table. -> Option D

4. Quick Check:

   Invalid column name causes error [OK]

Hint: Verify column names match exactly in both queries [OK]

Common Mistakes:

• Using wrong column names for correlation
• Assuming alias usage causes error
• Thinking GROUP BY is required in subquery

5. You want to find all departments where every employee earns more than the average salary of all employees in the company. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT d.name FROM departments d WHERE ALL (SELECT salary FROM employees WHERE department_id = d.id) > (SELECT AVG(salary) FROM employees);

B. SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees));

C. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees));

D. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.id));

Solution

1. Step 1: Understand the requirement

   We want departments where every employee earns more than the company-wide average salary.

2. Step 2: Analyze the NOT EXISTS query

   SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees)); uses NOT EXISTS to ensure no employee in the department earns less or equal to the company average, meaning all earn more.

3. Step 3: Check other options

   The options using EXISTS check for the existence of at least one qualifying employee, not all. The option attempting to use ALL with a subquery on the left side uses invalid syntax in MySQL.

4. Final Answer:

   SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees)); -> Option B

5. Quick Check:

   NOT EXISTS ensures all employees earn above average [OK]

Hint: Use NOT EXISTS to check all employees meet condition [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS for 'all' condition
• Misusing ALL keyword in MySQL
• Comparing with department average instead of company average