Nested Subqueries in SQL with 3+ Level Examples

A subquery can itself contain another subquery, which can contain yet another — there's no hard limit to how deeply queries can nest in SQL. While two-level subqueries are common, three-or-more-level nesting appears in genuinely complex business questions and is a favorite advanced interview topic, testing whether a candidate can hold multiple layers of logic in mind at once.

Key Definitions

• Nested subquery: A subquery that itself contains another subquery, creating multiple levels of query-within-query structure.
• Nesting level: The depth of a subquery relative to the outermost query — a subquery inside a subquery inside the main query is at nesting level two.

What You'll Learn

• Understand how subqueries can nest within other subqueries.
• Build a three-level nested query step by step, from the innermost query outward.
• Apply a systematic strategy for reading and debugging deeply nested queries.
• Recognize when deep nesting should be simplified using a derived table instead.

Detailed Explanation

Consider a three-level question: 'find patients from the same city as any patient who was treated by the highest-paid doctor.' Working from the innermost query outward makes this manageable. Level 1 (innermost): find the highest-paid doctor's ID — `(SELECT doctor_id FROM doctors WHERE salary = (SELECT MAX(salary) FROM doctors))`. Level 2: find patients treated by that doctor — `(SELECT patient_id FROM appointments WHERE doctor_id = <level 1 result>)`. Level 3: find those patients' cities, then find all patients from any of those cities — `(SELECT city FROM patients WHERE patient_id IN <level 2 result>)`, feeding into the outermost query's WHERE city IN (...).

The universal strategy for both writing and debugging nested queries is the same: build and test from the inside out. Write the innermost subquery first, run it standalone to confirm its result makes sense, then wrap it in the next layer, testing again, and repeat until the full nested structure is complete. Attempting to write all three levels at once, top-down, is a common source of frustrating, hard-to-locate bugs.

As nesting grows beyond two or three levels, readability often suffers. At that point, rewriting some levels as derived tables (Lesson 7.6) with clear aliases, or breaking the logic into a temporary table or view, frequently produces a more maintainable query than continuing to nest nested subqueries indefinitely.

Visual Summary

Three concentric boxes, innermost to outermost, each labeled with its role: innermost 'Level 1: Find highest-paid doctor's ID', middle 'Level 2: Find patients treated by that doctor', outer 'Level 3: Find those patients' cities, then all patients from those cities'. Arrows point outward from each level's result feeding into the next.

Quick Reference

Nesting Level	Question Answered	Feeds Into
Level 1 (innermost)	Who is the highest-paid doctor?	Level 2's WHERE doctor_id = ...
Level 2	Which patients did that doctor treat?	Level 3's WHERE patient_id IN ...
Level 3	Which cities are those patients from?	Outer query's WHERE city IN ...

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);

-- Three-level nested subquery, built from the inside out
SELECT DISTINCT patient_name, city
FROM patients
WHERE city IN (
  -- Level 3: cities of patients treated by the highest-paid doctor
  SELECT city FROM patients WHERE patient_id IN (
    -- Level 2: patients treated by the highest-paid doctor
    SELECT patient_id FROM appointments WHERE doctor_id = (
      -- Level 1: the highest-paid doctor's ID
      SELECT doctor_id FROM doctors
      WHERE salary = (SELECT MAX(salary) FROM doctors)
    )
  )
);

Reading from the innermost query outward: Level 1 finds Dr. Sen's doctor_id (103), since he has the highest salary (120000). Level 2 finds patient_id 204 (Divya Nair), who was treated by doctor 103. Level 3 finds Divya Nair's city, 'Pune'. The outermost query then returns every patient living in Pune, which includes both Amit Rao and Divya Nair, since both share that city.

Real-World Examples

• Marketing analytics platforms build multi-level nested queries to answer layered questions like 'find all customers in the same region as anyone who bought from our top-selling product's top-selling variant.'
• Academic research databases use deeply nested queries to trace multi-step relationships, such as finding co-authors of anyone who cited a specific paper's most-cited reference.
• Fraud investigation systems often build nested queries to trace multi-hop relationships between suspicious accounts.

Common Mistakes to Avoid

• Writing a deeply nested query top-down all at once instead of building and testing from the inside out.
• Losing track of which level of nesting a particular column or alias belongs to in a very deep query.
• Continuing to nest subqueries indefinitely instead of restructuring with derived tables once readability suffers.

Interview Questions

Q1. How deep can subqueries be nested in SQL?

There's no strict limit enforced by standard SQL or MySQL — subqueries can nest as many levels deep as the logic requires, though very deep nesting typically hurts readability and may benefit from being restructured using derived tables or temporary tables instead.

Q2. What is the recommended strategy for writing a deeply nested subquery?

Build and test from the inside out: write the innermost subquery first, run it independently to verify its result, then wrap it in the next outer layer and test again, repeating until the full nested structure is complete and correct.

Q3. When should deeply nested subqueries be rewritten using derived tables instead?

When nesting grows beyond two or three levels and readability starts to suffer, restructuring some levels as clearly aliased derived tables, or breaking the logic into a temporary table, often produces a more maintainable and debuggable query.

Practice MCQs

1. What is the recommended approach for building a multi-level nested subquery?

1. Write the outermost query first, then add inner layers
2. Write the innermost subquery first and test outward
3. Write all levels simultaneously
4. Nested subqueries cannot be tested individually

Answer: B. Write the innermost subquery first and test outward

Explanation: Building and verifying from the inside out ensures each layer's logic is correct before it's wrapped in the next, making debugging far more manageable.

2. What is a good alternative to excessive subquery nesting for readability?

1. Removing all WHERE clauses
2. Using derived tables or temporary tables to break up the logic
3. Combining all levels into a single WHERE condition
4. Avoiding subqueries entirely in all cases

Answer: B. Using derived tables or temporary tables to break up the logic

Explanation: Restructuring deeply nested logic into clearly aliased derived tables or temporary tables typically improves both readability and maintainability.

Quick Revision Points

• SQL does not enforce a strict maximum subquery nesting depth.
• The standard debugging strategy is building and testing from the innermost query outward.
• Excessive nesting should generally be refactored into derived tables or temporary tables for maintainability.

Conclusion

• Subqueries can nest multiple levels deep to answer genuinely multi-step business questions.
• Always build and test deeply nested queries from the inside out, one level at a time.
• Refactor into derived tables when nesting depth starts to hurt readability.

Subqueries can nest within subqueries to any depth SQL logic requires, and three-or-more-level nested queries appear in genuinely complex, multi-step business questions and advanced interview scenarios. The reliable strategy for both writing and debugging these queries is working from the innermost level outward — verifying each layer's result independently before wrapping it in the next. When nesting grows deep enough to hurt readability, restructuring the logic using clearly aliased derived tables or temporary tables is generally the better long-term choice over continuing to nest indefinitely.