What is a correlated subquery in SQL?

A correlated subquery is a subquery that references a column from the outer query, meaning it must be re-evaluated separately for each row the outer query processes, rather than running just once.

How is a correlated subquery different from a regular subquery?

A regular (non-correlated) subquery runs once independently of the outer query. A correlated subquery depends on the outer row's values and conceptually re-executes for every row.

What is a common use case for correlated subqueries?

Finding the maximum or minimum value within each group, such as the highest-paid employee in each department, is the classic use case, since the comparison value depends on which group the current row belongs to.

Are correlated subqueries slow?

They can be, since they conceptually execute once per outer row. On large tables, this can be significantly slower than an equivalent JOIN-based or window function approach, so performance should be tested.

How can I tell if a subquery is correlated just by looking at it?

Check whether the subquery references any column from the outer query's table (using the outer table's alias). If it does, the subquery is correlated; if it stands completely independent of the outer query, it is non-correlated.

What is a correlated subquery in SQL?

A correlated subquery is a subquery that references a column from the outer query, meaning it must be re-evaluated separately for each row the outer query processes, rather than running just once.

How is a correlated subquery different from a regular subquery?

A regular (non-correlated) subquery runs once independently of the outer query. A correlated subquery depends on the outer row's values and conceptually re-executes for every row.

What is a common use case for correlated subqueries?

Finding the maximum or minimum value within each group, such as the highest-paid employee in each department, is the classic use case, since the comparison value depends on which group the current row belongs to.

Are correlated subqueries slow?

They can be, since they conceptually execute once per outer row. On large tables, this can be significantly slower than an equivalent JOIN-based or window function approach, so performance should be tested.

How can I tell if a subquery is correlated just by looking at it?

Check whether the subquery references any column from the outer query's table (using the outer table's alias). If it does, the subquery is correlated; if it stands completely independent of the outer query, it is non-correlated.

Correlated Subquery in SQL with Row-by-Row Examples

Most subqueries run once, produce a result, and hand it off to the outer query. A correlated subquery is fundamentally different: it references a column from the outer query, which means it cannot be evaluated in isolation — it must be re-run once for every single row the outer query considers, almost like a mini function call repeated row by row.

Key Definitions

Correlated subquery: A subquery that references a column from the outer query, requiring it to be re-evaluated separately for every row the outer query processes.
Outer reference: A column from the outer query used inside the subquery, which is what makes a subquery correlated rather than independent.

What You'll Learn

Define a correlated subquery and how it differs from a non-correlated subquery.
Trace the row-by-row execution of a correlated subquery.
Write a correlated subquery solving a 'per-group maximum' problem.
Understand the performance implications of row-by-row re-execution.

Detailed Explanation

Consider finding each department's highest-paid doctor: `SELECT doctor_name, department_id, salary FROM doctors d1 WHERE salary = (SELECT MAX(salary) FROM doctors d2 WHERE d2.department_id = d1.department_id)`. Walk through this row by row: for Dr. Verma (department_id = 1), the inner subquery runs with d1.department_id fixed at 1, computing MAX(salary) only among doctors in department 1 — which includes Dr. Verma and Dr. Khan — giving 95000. For Dr. Sen (department_id = 3), the subquery re-runs, this time filtered to department 3 alone, giving 120000. Each outer row triggers a fresh, differently-filtered execution of the inner query.

This row-by-row dependency is exactly what the term 'correlated' means — the inner query is correlated with, tied to, the current outer row via d1.department_id. Because of this repeated execution, correlated subqueries can become a genuine performance concern on large tables, since a query with 100,000 outer rows could trigger 100,000 separate inner query executions unless MySQL's optimizer can rewrite it more efficiently internally.

Visual Summary

A vertical sequence of four labeled steps, each showing one outer row triggering a differently-scoped inner query: 'Row 1: Dr. Verma (dept 1) → inner query filters dept=1 → MAX=95000', 'Row 2: Dr. Iyer (dept 2) → inner query filters dept=2 → MAX=78000', 'Row 3: Dr. Sen (dept 3) → inner query filters dept=3 → MAX=120000', 'Row 4: Dr. Khan (dept 1) → inner query filters dept=1 → MAX=95000'.

Quick Reference

Outer Row (doctor)	Inner Subquery Filter	Inner Query Result (MAX salary)
Dr. Verma, dept 1, salary 95000	department_id = 1	95000 → matches, included
Dr. Iyer, dept 2, salary 78000	department_id = 2	78000 → matches, included
Dr. Sen, dept 3, salary 120000	department_id = 3	120000 → matches, included
Dr. Khan, dept 1, salary 88000	department_id = 1	95000 → does not match, excluded

SQL Example


CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Correlated subquery: find the highest-paid doctor in each department
SELECT d1.doctor_name, d1.department_id, d1.salary
FROM doctors d1
WHERE d1.salary = (
  SELECT MAX(d2.salary)
  FROM doctors d2
  WHERE d2.department_id = d1.department_id
);

The subquery references d1.department_id, a column from the outer query, making it correlated. For each outer row, the inner query is re-scoped to only that row's department before computing MAX(salary). Dr. Verma, Dr. Iyer, and Dr. Sen each turn out to be their department's top earner and appear in the result, while Dr. Khan, despite a respectable salary, is excluded because Dr. Verma earns more within their shared department.

Real-World Examples

HR systems use correlated subqueries to find the highest-paid employee within each department for compensation benchmarking.
E-commerce platforms use correlated subqueries to find the best-selling product within each category.
Sports analytics use correlated subqueries to find the top scorer within each team for a given season.

Common Mistakes to Avoid

Confusing a correlated subquery with a non-correlated one, missing that a column reference to the outer query is what makes it correlated.
Using correlated subqueries carelessly on very large tables without considering the performance impact of repeated execution.
Forgetting to alias both the outer and inner references to the same table (like d1 and d2), causing ambiguous column errors.

Interview Questions

Q1. What makes a subquery correlated?

A subquery is correlated when it references a column from the outer query. This dependency means the subquery cannot be evaluated independently and must be re-run separately for each row processed by the outer query.

Q2. How does a correlated subquery execute differently from a non-correlated subquery?

A non-correlated subquery executes once, producing a single result reused across the outer query. A correlated subquery re-executes once per outer row, each time filtered or scoped differently based on that row's specific column values.

Q3. Give a classic example of a problem that requires a correlated subquery.

Finding the maximum (or minimum) value within each group — such as the highest-paid employee in each department — where the comparison value itself depends on which group the current outer row belongs to.

Practice MCQs

1. What defines a correlated subquery?

It always returns multiple rows
It references a column from the outer query
It must be placed in the FROM clause
It can only use aggregate functions

Answer: B. It references a column from the outer query

Explanation: A correlated subquery is defined by its dependency on the outer query's current row via a referenced column, forcing re-execution per row.

2. How many times does a correlated subquery typically execute for an outer query processing 50 rows?

Once
50 times, once per outer row
Exactly twice
It depends only on table size, not row count

Answer: B. 50 times, once per outer row

Explanation: Because the subquery depends on each outer row's specific values, it conceptually re-executes separately for every row the outer query processes.

Quick Revision Points

A subquery is correlated if and only if it references a column from the outer query.
Correlated subqueries conceptually execute once per outer row, unlike non-correlated subqueries which execute once total.
Finding the max/min value per group is the classic textbook use case for a correlated subquery.

Conclusion

A correlated subquery references the outer query, requiring per-row re-evaluation.
This makes correlated subqueries powerful for per-group comparisons, like finding the top value within each group.
Performance should be considered carefully on large tables, since correlated subqueries can execute many times.

A correlated subquery references a column from the outer query, creating a dependency that forces it to be conceptually re-executed once for every row the outer query processes, each time scoped differently based on that row's values. This makes correlated subqueries the natural tool for per-group comparison problems, such as finding the highest-paid doctor within each department, where the comparison value itself depends on which group the current row belongs to. Because of this row-by-row execution pattern, correlated subqueries deserve careful performance consideration on large tables.