Correlated subquery execution model in SQL - Deep Dive

Overview - Correlated subquery execution model

What is it?

A correlated subquery is a type of query inside another query where the inner query depends on values from the outer query. It runs once for each row processed by the outer query, using that row's data to filter or calculate results. This makes it different from a regular subquery, which runs only once. Correlated subqueries help answer questions that need row-by-row comparison or filtering.

Why it matters

Without correlated subqueries, it would be hard to express queries that compare each row to related data in the same or another table. They allow databases to perform complex filtering and calculations that depend on each row's context. Without them, queries would be less flexible and more complicated, making data analysis slower and more error-prone.

Where it fits

Before learning correlated subqueries, you should understand basic SQL queries, joins, and simple subqueries. After mastering correlated subqueries, you can explore query optimization, window functions, and advanced SQL performance tuning.

Mental Model

Core Idea

A correlated subquery runs repeatedly, once for each row of the outer query, using that row's data to produce a result.

Think of it like...

It's like a teacher grading each student's test by checking a separate answer sheet for each student individually, rather than grading all tests at once.

Outer Query Row 1 ──▶ Inner Query uses Row 1 data ──▶ Result 1
Outer Query Row 2 ──▶ Inner Query uses Row 2 data ──▶ Result 2
Outer Query Row 3 ──▶ Inner Query uses Row 3 data ──▶ Result 3

Each outer row triggers a new inner query execution.

Build-Up - 6 Steps

1

Foundation

Understanding Basic Subqueries

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

A subquery is a query nested inside another query. It runs once and returns a value or set of values used by the outer query. For example, finding employees whose salary is above the average salary uses a subquery to calculate the average first.

Result

The outer query filters employees based on the single result from the subquery.

Understanding that subqueries can return values for filtering or calculation is the base for grasping correlated subqueries.

2

Foundation

Difference Between Simple and Correlated Subqueries

Concept: Recognize that correlated subqueries depend on outer query rows, unlike simple subqueries.

Simple subqueries run once and do not depend on outer query rows. Correlated subqueries reference columns from the outer query, so they run once per outer row. For example, finding employees whose salary is greater than the average salary in their own department uses a correlated subquery referencing the department of each employee.

Result

The inner query runs multiple times, each time with different outer row data.

Knowing this difference helps predict query behavior and performance implications.

3

Intermediate

How Correlated Subqueries Execute Step-by-Step

🤔Before reading on: do you think the inner query runs once or multiple times for the whole outer query? Commit to your answer.

Concept: Learn the repeated execution process of correlated subqueries for each outer row.

For each row in the outer query, the database runs the inner query using that row's values. This means if the outer query processes 100 rows, the inner query runs 100 times. The inner query result then influences whether the outer row is included or how it is processed.

Result

The query result reflects row-by-row evaluation, allowing precise filtering or calculation.

Understanding this repeated execution explains why correlated subqueries can be slower and how they achieve row-specific logic.

4

Intermediate

Common Use Cases for Correlated Subqueries

🤔Before reading on: do you think correlated subqueries are mainly used for filtering, aggregation, or joining? Commit to your answer.

Concept: Identify typical scenarios where correlated subqueries solve problems elegantly.

Correlated subqueries are often used to filter rows based on related data, calculate aggregates per row group, or check existence of related records. For example, finding customers who placed orders above their average order amount or checking if a product has any reviews.

Result

Queries become more readable and expressive for complex row-dependent conditions.

Knowing these use cases helps choose the right tool for complex queries instead of forcing joins or multiple queries.

5

Advanced

Performance Implications of Correlated Subqueries

🤔Before reading on: do you think correlated subqueries are generally faster, slower, or the same speed as joins? Commit to your answer.

Concept: Understand how repeated inner query execution affects query speed and resource use.

Because the inner query runs once per outer row, correlated subqueries can be slow on large datasets. Databases may optimize by rewriting queries or caching results, but sometimes joins or window functions perform better. Knowing when to rewrite correlated subqueries is key for performance.

Result

Query execution time can increase significantly with correlated subqueries on big tables.

Understanding performance trade-offs guides writing efficient queries and avoiding slowdowns.

6

Expert

Database Engine Optimization of Correlated Subqueries

🤔Before reading on: do you think databases always run correlated subqueries literally as written? Commit to your answer.

Concept: Learn how modern databases internally transform correlated subqueries for efficiency.

Many database engines rewrite correlated subqueries into joins or apply caching and indexing strategies to reduce repeated work. Some engines use nested loop joins or apply decorrelation techniques to convert correlated subqueries into more efficient forms. Understanding these internals helps write queries that the engine can optimize well.

Result

Queries with correlated subqueries may run faster than expected due to engine optimizations.

Knowing internal optimizations helps write queries that leverage database strengths and avoid pitfalls.

Under the Hood

When a correlated subquery runs, the database processes the outer query row by row. For each outer row, it substitutes the outer row's values into the inner query and executes it. This repeated execution can be costly. Internally, the database may use nested loops or transform the query into joins to improve speed. The execution plan shows how many times the inner query runs and what indexes are used.

Why designed this way?

Correlated subqueries were designed to allow flexible, row-dependent filtering and calculations without requiring complex joins or multiple queries. They provide a natural way to express conditions that depend on each row's data. Alternatives like joins can be less intuitive or harder to write for some problems. The tradeoff is potential performance cost, which databases try to mitigate with optimizations.

┌───────────────┐       ┌─────────────────────┐
│ Outer Query   │──────▶│ Inner Query executed │
│ processes row │       │ using outer row data │
│ 1             │       └─────────────────────┘
│               │
│ Outer Query   │──────▶┌─────────────────────┐
│ processes row │       │ Inner Query executed │
│ 2             │       │ using outer row data │
│               │       └─────────────────────┘
│ ...           │
│ Outer Query   │──────▶┌─────────────────────┐
│ processes row │       │ Inner Query executed │
│ N             │       │ using outer row data │
└───────────────┘       └─────────────────────┘

Myth Busters - 3 Common Misconceptions

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

Common Belief: A correlated subquery runs only once, just like a simple subquery.

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

Why it matters: Assuming it runs once leads to underestimating query cost and performance issues on large datasets.

Quick: Can you always replace a correlated subquery with a join without changing results? Commit to yes or no.

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

Reality: While sometimes interchangeable, correlated subqueries can express conditions that are hard or impossible to replicate exactly with joins.

Why it matters: Blindly replacing correlated subqueries with joins can change query meaning or produce incorrect results.

Quick: Do database engines always execute correlated subqueries literally as written? Commit to yes or no.

Common Belief: The database runs correlated subqueries exactly as the query text states, without optimization.

Reality: Modern databases often rewrite or optimize correlated subqueries internally to improve performance.

Why it matters: Knowing this helps write queries that the engine can optimize, avoiding slow queries.

Expert Zone

1

Some correlated subqueries can be decorrelated by the database engine, turning them into joins for better performance.

2

Indexing the columns used in the correlated subquery's WHERE clause can drastically improve execution speed.

3

Correlated subqueries inside SELECT clauses behave differently than those in WHERE clauses, affecting execution plans.

When NOT to use

Avoid correlated subqueries on very large tables or when performance is critical; consider rewriting as joins, using window functions, or applying temporary tables instead.

Production Patterns

In real systems, correlated subqueries are used for row-level validations, existence checks, and conditional aggregations. They often appear in reporting queries and data quality checks where row context matters.

Connections

Nested Loops Algorithm

Correlated subqueries often execute using nested loops internally.

Understanding nested loops in algorithms helps grasp why correlated subqueries can be slow and how indexes help.

Functional Programming Closures

Correlated subqueries capture outer query variables like closures capture outer variables.

Knowing closures in programming clarifies how inner queries depend on outer query data dynamically.

Iterative Processes in Manufacturing

Correlated subquery execution resembles iterative checking of each item in a production line.

Seeing correlated subqueries as repeated checks helps understand their cost and necessity for precision.

Common Pitfalls

#1 Writing a correlated subquery that runs on a large table without indexes.

Wrong approach:

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

Correct approach:

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

Root cause:

Not indexing columns used in the correlated subquery causes full table scans each time, slowing execution.

#2 Replacing a correlated subquery with a join that changes query meaning.

Wrong approach:

SELECT e.name FROM employees e JOIN employees d ON e.department = d.department WHERE e.salary > d.salary;

Correct approach:

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

Root cause:

Misunderstanding the logic difference between correlated subqueries and joins leads to incorrect results.

#3 Assuming correlated subqueries always perform poorly and avoiding them blindly.

Wrong approach:

Rewriting all correlated subqueries as joins without testing performance.

Correct approach:

Analyze query plans and test performance; use correlated subqueries when they express logic clearly and perform well.

Root cause:

Lack of understanding of database optimizations and query plan analysis causes premature optimization.

Key Takeaways

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

They allow expressing row-dependent conditions that are hard to write with joins alone.

Repeated execution can cause performance issues, but database engines often optimize these queries internally.

Proper indexing and understanding query plans are essential to use correlated subqueries efficiently.

Knowing when and how to use correlated subqueries is key to writing clear, correct, and performant SQL.

Practice

1. What is a correlated subquery in SQL?

easy

A. A subquery that runs independently of the outer query

B. A subquery that uses values from the outer query to filter results

C. A query that joins two tables without conditions

D. A query that only returns aggregate values

Solution

1. Step 1: Understand subquery types

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

2. Step 2: Identify correlation

   It uses columns from the outer query inside the subquery's WHERE clause.

3. Final Answer:

   A subquery that uses values from the outer query to filter results -> Option B

4. Quick Check:

   Correlated subquery = uses outer query values [OK]

Hint: Correlated subqueries reference outer query columns [OK]

Common Mistakes:

• Thinking subquery runs once independently
• Confusing with JOIN operations
• Assuming it returns only aggregates

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

easy

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

B. SELECT e.name FROM employees e WHERE salary > 50000

C. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees)

D. SELECT e.name FROM employees e JOIN departments d ON e.department = d.id

Solution

1. Step 1: Identify correlation in subquery

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) uses 'e.department' inside the subquery, linking it to the outer query.

2. Step 2: Check other options

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees) has no reference to outer query in subquery; the option with 'salary > 50000' lacks a subquery and the JOIN option is not a subquery.

3. Final Answer:

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) -> Option A

4. Quick Check:

   Correlation needs outer query column inside subquery [OK]

Hint: Look for outer query columns inside subquery WHERE clause [OK]

Common Mistakes:

• Missing outer query reference inside subquery
• Confusing JOIN with subquery
• Using subquery without correlation

3. Given the tables employees(id, name, department, salary) and the query:

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

What does this query return?

medium

A. Employees whose salary is above the average salary of their own department

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

C. Employees with salary above 50000

D. All employees regardless of salary

Solution

1. Step 1: Understand the subquery correlation

   The subquery calculates average salary for the department of the current employee (e1.department).

2. Step 2: Compare salaries

   The outer query selects employees whose salary is greater than that department average.

3. Final Answer:

   Employees whose salary is above the average salary of their own department -> Option A

4. Quick Check:

   Salary > department average = Employees whose salary is above the average salary of their own department [OK]

Hint: Check which outer column is used inside subquery condition [OK]

Common Mistakes:

• Assuming average is for all employees
• Ignoring the correlation condition
• Confusing with simple WHERE salary > value

4. Identify the error in the following correlated subquery:

SELECT c.customer_id FROM customers c WHERE c.orders_count > (SELECT AVG(o.orders_count) FROM orders o WHERE o.customer_id = c.customer_id);

medium

A. The subquery will return multiple rows causing an error

B. The subquery uses a wrong table alias 'o' which is not defined

C. The subquery compares orders_count incorrectly; should use SUM instead of AVG

D. The subquery references the outer query correctly; no error

Solution

1. Step 1: Analyze correlation

   The subquery correctly uses 'c.customer_id' from the outer query in its WHERE clause.

2. Step 2: Check aggregation and output

   AVG(o.orders_count) is an aggregate that returns a single scalar value, even for customers with multiple orders.

3. Final Answer:

   The subquery references the outer query correctly; no error -> Option D

4. Quick Check:

   Subquery must return single value for comparison [OK]

Hint: Ensure subquery returns one value for comparison [OK]

Common Mistakes:

• Thinking AVG returns multiple rows without GROUP BY
• Assuming alias 'o' is undefined
• Believing SUM is needed instead of AVG

5. You want to find all products whose price is higher than the average price of products in the same category. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT p.product_name FROM products p JOIN categories c ON p.category = c.id WHERE p.price > c.avg_price

B. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products)

C. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category)

D. SELECT product_name FROM products WHERE price > ALL (SELECT price FROM products)

Solution

1. Step 1: Identify correlation condition

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) uses 'p.category' inside the subquery to calculate average price per category, correlating outer and inner queries.

2. Step 2: Verify other options

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products) compares to overall average, not per category; the option using JOIN with categories uses a JOIN but no subquery; the option using > ALL compares to all individual prices, not average.

3. Final Answer:

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) -> Option C

4. Quick Check:

   Correlated subquery filters by category [OK]

Hint: Use outer query column inside subquery WHERE for correlation [OK]

Common Mistakes:

• Using overall average instead of per category
• Confusing JOIN with subquery
• Using ALL instead of AVG in subquery