What is a Correlated Subquery in SQL?

A Correlated Subquery in SQL is a subquery that depends on values from the outer query.

In simple words:

A correlated subquery executes repeatedly for every row processed by the outer query.

Why Correlated Subqueries are Important

In enterprise applications:

Row-by-row comparisons are needed
Department-wise calculations are required
Dynamic filtering logic is common

Correlated subqueries help:

Compare rows dynamically
Perform dependent calculations
Implement advanced business rules

Simple Real-Life Example

Think about:

Finding employees whose salary is above their department average

Requirement

For each employee:

Calculate average salary of that employee's department
Compare employee salary against department average

This Requires

Inner query depends on:

Current row of outer query

Correlated Subquery Internal Architecture

Outer Query Reads One Row
          |
          v
Inner Query Uses Outer Row Value
          |
          v
Inner Query Executes
          |
          v
Result Returned to Outer Query
          |
          v
Repeat for Next Row

Main Difference from Normal Subquery

Feature	Normal Subquery	Correlated Subquery
Dependency	Independent	Depends on outer query
Execution Count	Usually once	Executes repeatedly
Performance	Faster	Slower
Complex Logic	Limited	Advanced row comparisons

Basic Correlated Subquery Syntax

SELECT column_name

FROM table1 outer_table

WHERE condition operator (

    SELECT aggregate_function(column_name)

    FROM table2 inner_table

    WHERE outer_table.column_name =
          inner_table.column_name

);

Employees Table Example

Employee	Department	Salary
Naresh	IT	90000
Rahul	IT	70000
Arjun	HR	60000
David	HR	50000

Requirement

Find employees earning more than their department average salary.

Correlated Subquery Example

SELECT e1.employee_name,
       e1.salary,
       e1.department

FROM employees e1

WHERE e1.salary > (

    SELECT AVG(e2.salary)

    FROM employees e2

    WHERE e1.department = e2.department

);

How This Works Internally

Step 1

Outer query reads:

Naresh | IT | 90000

Step 2

Inner query becomes:

SELECT AVG(salary)

FROM employees

WHERE department = 'IT';

Result

Step 3

Condition checks:

90000 > 80000

Result

Naresh selected.

Then Process Repeats

For:

Rahul
Arjun
David

Correlated Subquery Query Flow

Outer Query Reads Row
          |
          v
Pass Row Value to Inner Query
          |
          v
Inner Query Executes
          |
          v
Condition Evaluated
          |
          v
Move to Next Outer Row

Why Correlated Subqueries are Slower

Because:

Inner query executes once for every outer row

Example

If:

Outer query has 10,000 rows

then:

Inner query may execute 10,000 times

Normal Subquery Example

SELECT name

FROM students

WHERE marks > (

    SELECT AVG(marks)
    FROM students

);

What Happens?

Inner query executes:

Only once

Correlated Subquery Example

SELECT e1.employee_name

FROM employees e1

WHERE salary > (

    SELECT AVG(salary)

    FROM employees e2

    WHERE e1.department = e2.department

);

What Happens?

Inner query executes:

For every employee row

Correlated Subquery with EXISTS

Correlated subqueries are commonly used with:

EXISTS operator

Example

SELECT s.student_name

FROM students s

WHERE EXISTS (

    SELECT 1

    FROM enrollments e

    WHERE s.student_id = e.student_id

);

What Happens?

For each student:

Check if enrollment exists

Correlated Subquery with NOT EXISTS

SELECT s.student_name

FROM students s

WHERE NOT EXISTS (

    SELECT 1

    FROM enrollments e

    WHERE s.student_id = e.student_id

);

Result

Students without enrollments.

Correlated Subquery vs JOIN

Feature	Correlated Subquery	JOIN
Performance	Usually slower	Usually faster
Readability	Good for nested logic	Good for relationships
Execution Style	Repeated execution	Single optimized query

Why JOIN is Faster?

Because:

Database optimizer handles joins efficiently

Equivalent JOIN Example

SELECT e1.employee_name,
       e1.salary

FROM employees e1

JOIN (

    SELECT department,
           AVG(salary) AS avg_salary

    FROM employees

    GROUP BY department

) e2

ON e1.department = e2.department

WHERE e1.salary > e2.avg_salary;

Performance Optimization

To optimize correlated subqueries:

Use indexes
Rewrite using JOINs when possible

Real-Time Banking Example

Banking systems use correlated subqueries for:

Customer balance comparisons
Fraud detection
Branch-level analytics

Example

Find customers whose balance is above branch average.

Real-Time E-Commerce Example

E-commerce platforms use correlated subqueries for:

Category-wise pricing analysis
Order comparisons
Sales analytics

Example

Find products priced above category average.

Real-Time Learning Platform Example

Learning platforms use correlated subqueries for:

Student performance analysis
Course comparisons
Department rankings

Example

Find students scoring above course average.

Correlated Subqueries in Microservices

Microservices use correlated subqueries for:

Analytics APIs
Reporting dashboards
Complex business calculations

Advantages of Correlated Subqueries

Supports advanced row comparisons
Easy to express dependent logic
Useful for complex business rules

Disadvantages of Correlated Subqueries

Slow performance on large datasets
Repeated query execution
Higher database load

Performance Consideration

Correlated subqueries should be:

Used carefully on large tables

Best Practices

Use indexes on compared columns
Rewrite using JOINs when possible
Avoid unnecessary correlated queries
Analyze execution plans

Common Interview Mistake

Many developers think:

Correlated subqueries execute only once

Reality

Correlated subqueries:

Execute repeatedly for outer query rows

Professional Interview Answer

A Correlated Subquery in SQL is a subquery that depends on values from the outer query and executes repeatedly for each row processed by the outer query. Unlike normal subqueries that execute independently, correlated subqueries dynamically reference outer query columns using table aliases. They are commonly used for row-by-row comparisons, department-wise calculations, existence checks, and advanced business logic. Although correlated subqueries provide powerful query capabilities, they may impact performance because the inner query executes multiple times, and JOIN operations are often preferred for optimization in large-scale enterprise systems.

Why Interviewers Like This Answer

Clearly explains dependency between queries
Includes execution flow understanding
Shows performance trade-off knowledge
Provides real-world examples
Explains JOIN optimization concepts

Frequently Asked Questions