Correlated subqueries in MySQL - Deep Dive

Overview - Correlated subqueries

What is it?

A correlated subquery is a special kind of query inside another query that depends on the outer query for its values. It runs once for each row processed by the outer query, using data from that row to complete its work. This makes it different from a regular subquery, which runs just once. Correlated subqueries help answer questions that need row-by-row comparisons or calculations.

Why it matters

Without correlated subqueries, it would be hard to compare each row in a table to related data in the same or another table dynamically. They let you write powerful queries that adapt to each row's data, making your database answers more precise and flexible. Without them, you'd need complex workarounds or multiple queries, which slow down your work and increase errors.

Where it fits

Before learning correlated subqueries, you should understand basic SQL queries, simple subqueries, and how tables relate to each other. After mastering correlated subqueries, you can explore advanced SQL topics like window functions, joins with aggregation, and query optimization techniques.

Mental Model

Core Idea

A correlated subquery is a query inside another query that uses data from the outer query to run once per row, making the inner query depend on each outer row.

Think of it like...

Imagine you are checking each student's test score and, for each student, you look up their class average to compare. You do this check one student at a time, using that student's class info to find the right average. The inner lookup depends on the student you are currently checking.

Outer Query Row ──▶ Inner Query uses Outer Row Data
┌───────────────┐     ┌─────────────────────┐
│ Outer Query   │     │ Correlated Subquery  │
│ (each row)   ─┼────▶│ uses outer row value │
└───────────────┘     └─────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic Subqueries

Concept: Learn what a subquery is and how it runs independently inside another query.

A subquery is a query nested inside another query. It runs once and returns a result used by the outer query. For example, to find employees who earn more than the average salary, you can write: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Result

The query returns all employees earning more than the average salary.

Understanding that subqueries run independently and return a fixed result helps you see why correlated subqueries are different.

2

Foundation

How Outer and Inner Queries Work Together

Concept: Learn the roles of outer and inner queries and how they interact in SQL.

In a nested query, the outer query selects rows from a table, and the inner query provides data used by the outer query. Usually, the inner query runs once, but sometimes it needs to run for each outer row, which leads to correlated subqueries.

Result

You understand the basic structure of nested queries and the difference between independent and dependent inner queries.

Knowing the separation of outer and inner queries sets the stage for understanding when the inner query depends on the outer query.

3

Intermediate

Defining Correlated Subqueries

🤔Before reading on: do you think a correlated subquery runs once or multiple times? Commit to your answer.

Concept: Introduce the idea that a correlated subquery runs once per outer query row, using outer row data inside the inner query.

A correlated subquery uses a column from the outer query inside its WHERE clause or SELECT list. Because it depends on the outer row, it must run again for each row the outer query processes. For example: SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e1.department); Here, the inner query calculates the average salary for the department of the current employee row.

Result

The query returns employees who earn more than the average salary in their own department.

Understanding that the inner query depends on the outer row explains why it runs multiple times and how it customizes results per row.

4

Intermediate

Performance Considerations of Correlated Subqueries

🤔Before reading on: do you think correlated subqueries are faster or slower than regular subqueries? Commit to your answer.

Concept: Explore how correlated subqueries can impact query speed because they run repeatedly for each outer row.

Because a correlated subquery runs once per outer row, it can be slow on large tables. For example, if the outer query returns 1000 rows, the inner query runs 1000 times. This repeated execution can cause performance issues. Sometimes, rewriting the query using JOINs or window functions can be faster.

Result

You recognize that correlated subqueries can cause slow queries and that alternatives might be needed for large datasets.

Knowing the performance cost helps you decide when to use correlated subqueries and when to seek more efficient solutions.

5

Intermediate

Using Correlated Subqueries in SELECT and WHERE

Concept: Learn that correlated subqueries can appear in different parts of a query, like SELECT or WHERE clauses.

Correlated subqueries are not limited to WHERE clauses. They can also appear in SELECT to calculate values per row. For example: SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count FROM customers c; This counts orders per customer dynamically.

Result

The query returns each customer with their total number of orders.

Seeing correlated subqueries in SELECT shows their flexibility to compute row-specific values on the fly.

6

Advanced

Rewriting Correlated Subqueries with JOINs

🤔Before reading on: do you think all correlated subqueries can be replaced by JOINs? Commit to your answer.

Concept: Learn how to rewrite correlated subqueries as JOINs to improve performance and readability.

Many correlated subqueries can be rewritten using JOINs and GROUP BY. For example, the previous example counting orders per customer can be: SELECT c.name, COUNT(o.id) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id; This often runs faster because the database can optimize JOINs better than repeated subqueries.

Result

The rewritten query returns the same results but can be more efficient.

Knowing how to rewrite correlated subqueries as JOINs helps optimize queries and avoid performance pitfalls.

7

Expert

Internal Execution and Optimization of Correlated Subqueries

🤔Before reading on: do you think databases always run correlated subqueries literally once per outer row? Commit to your answer.

Concept: Understand how database engines execute and optimize correlated subqueries internally, sometimes caching or transforming them.

Though conceptually a correlated subquery runs once per outer row, modern databases use optimization techniques like caching results, rewriting queries internally, or using semi-joins to reduce repeated work. However, these optimizations depend on the database engine and query complexity. Understanding this helps write queries that the optimizer can handle well.

Result

You realize that correlated subqueries are not always as slow as they seem and that database internals affect performance.

Knowing internal optimizations helps you write better queries and understand why some correlated subqueries perform well while others don't.

Under the Hood

A correlated subquery is executed by the database engine as a nested loop: for each row in the outer query, the inner query runs using that row's values. This means the inner query is re-evaluated repeatedly, often causing many executions. The database may optimize by caching or rewriting the query plan, but fundamentally it depends on the outer row's data to produce its result.

Why designed this way?

Correlated subqueries were designed to allow flexible, row-dependent filtering or calculations that simple joins or independent subqueries cannot express easily. They provide a natural way to express queries that compare each row to related data dynamically. Alternatives like joins or window functions came later and sometimes offer better performance, but correlated subqueries remain a clear and intuitive tool.

Outer Query Rows
┌───────────────┐
│ Row 1         │
│ Row 2         │
│ ...           │
│ Row N         │
└─────┬─────────┘
      │
      ▼
┌─────────────────────────────┐
│ Correlated Subquery Execution│
│ For each outer row:          │
│   Run inner query using row  │
│   data as input              │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a correlated subquery run only once for the entire query? Commit yes or no.

Common Belief: A correlated subquery runs only once like a normal subquery.

Reality: A correlated subquery runs once for each row processed by the outer query, using that row's data.

Why it matters: Assuming it runs once leads to underestimating performance costs and writing inefficient queries.

Quick: Can all correlated subqueries be safely replaced by JOINs? Commit yes or no.

Common Belief: Every correlated subquery can be rewritten as a JOIN without changing results.

Reality: Some correlated subqueries express logic that JOINs cannot replicate exactly, especially with complex filters or aggregations.

Why it matters: Trying to rewrite all correlated subqueries as JOINs can cause incorrect results or overly complex queries.

Quick: Does using a correlated subquery always slow down your query? Commit yes or no.

Common Belief: Correlated subqueries always cause slow queries and should be avoided.

Reality: While often slower, some correlated subqueries run efficiently due to database optimizations or small data sizes.

Why it matters: Avoiding correlated subqueries blindly can lead to more complex or less readable queries without real benefit.

Quick: Is a correlated subquery the same as a join? Commit yes or no.

Common Belief: Correlated subqueries and joins are the same and interchangeable.

Reality: They are different concepts; correlated subqueries run repeatedly per row, while joins combine tables in one operation.

Why it matters: Confusing them leads to misunderstanding query behavior and performance characteristics.

Expert Zone

1

Some databases optimize correlated subqueries by transforming them into semi-joins internally, which can drastically improve performance without changing query syntax.

2

Correlated subqueries can sometimes cause unexpected NULL results if the inner query returns no rows, which affects filtering logic and requires careful handling.

3

Using correlated subqueries inside EXISTS clauses often performs better than using IN with subqueries, especially when dealing with large datasets.

When NOT to use

Avoid correlated subqueries when working with very large tables or when performance is critical; instead, use JOINs, window functions, or temporary tables. Also, avoid them when the logic can be expressed more clearly with aggregation or analytic functions.

Production Patterns

In real systems, correlated subqueries are often used for row-level filtering, such as finding records with values above group averages or checking existence of related records. They appear in reports, data validation, and conditional calculations. Experienced developers combine them with indexes and query hints to balance clarity and performance.

Connections

Window Functions

Builds-on and alternative

Understanding correlated subqueries helps grasp window functions, which provide similar row-by-row calculations but often more efficiently and with clearer syntax.

Nested Loops in Programming

Same pattern of repeated execution

Correlated subqueries behave like nested loops in programming, running inner logic repeatedly for each outer item, which explains their performance characteristics.

Functional Programming Closures

Similar concept of inner function depending on outer scope

Correlated subqueries resemble closures where an inner function uses variables from an outer function, showing a cross-domain pattern of dependency and repeated evaluation.

Common Pitfalls

#1 Writing a correlated subquery that returns multiple rows where only one is expected.

Wrong approach:

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

Correct approach:

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

Root cause:

The inner query returns multiple salaries instead of a single value, causing an error or unexpected behavior.

#2 Using correlated subqueries unnecessarily when a JOIN would be simpler and faster.

Wrong approach:

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

Correct approach:

SELECT e1.name FROM employees e1 JOIN (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) d ON e1.department = d.department WHERE e1.salary > d.avg_salary;

Root cause:

Not recognizing that aggregation with JOIN can replace the correlated subquery for better performance.

#3 Assuming correlated subqueries always perform poorly and avoiding them even when appropriate.

Wrong approach:

Rewriting simple correlated subqueries into complex joins or multiple queries unnecessarily.

Correct approach:

Using correlated subqueries when they clearly express the logic and performance is acceptable.

Root cause:

Misunderstanding that correlated subqueries can be efficient with small data or good indexing.

Key Takeaways

Correlated subqueries run once per row of the outer query, using that row's data inside the inner query.

They allow dynamic, row-specific filtering or calculations that simple subqueries or joins cannot always express.

Because they run repeatedly, correlated subqueries can cause performance issues on large datasets, so alternatives like JOINs or window functions may be better.

Modern databases optimize correlated subqueries internally, so understanding their execution helps write efficient queries.

Knowing when and how to use correlated subqueries is essential for writing clear, powerful, and maintainable SQL.

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