SQL Topics
Nested Subqueries
title: Nested Subqueries
In the previous lesson, you learned that a subquery is a query written inside another query. A subquery allows SQL to perform one operation and use its result in another operation.
However, in real-world applications, some business problems are too complex for a single subquery.
For example:
- Find employees who work in the department with the highest budget.
- Find products belonging to the category with the most sales.
- Find customers who placed orders in the city with the highest revenue.
- Find students enrolled in the course with the highest average score.
These situations often require multiple levels of querying.
To solve such problems, SQL allows a subquery to contain another subquery. This creates a structure known as a Nested Subquery.
Nested Subqueries are commonly used in reporting systems, analytics platforms, financial applications, and enterprise databases.
What is a Nested Subquery?
A Nested Subquery is a subquery placed inside another subquery.
Structure:
Outer Query
↓
Subquery
↓
Another SubqueryExample:
SELECT EmployeeName
FROM Employees
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Departments
WHERE Budget =
(
SELECT MAX(Budget)
FROM Departments
)
);In this query:
Level 1 → Outer Query
Level 2 → First Subquery
Level 3 → Second SubqueryMultiple query levels work together to produce the final result.
Why are Nested Subqueries Important?
Imagine a company database.
Management asks:
Find employees working in
the department that has
the highest budget.To answer this:
Step 1
Find the highest department budget.
Step 2
Find the department that owns that budget.
Step 3
Find employees working in that department.
This requires multiple levels of querying.
Nested Subqueries solve this problem efficiently.
How Nested Subqueries Work
Consider:
SELECT EmployeeName
FROM Employees
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Departments
WHERE Budget =
(
SELECT MAX(Budget)
FROM Departments
)
);Execution order:
SQL executes from the innermost query outward.
Visual Representation
Outer Query
│
▼
First Subquery
│
▼
Second Subquery
│
▼
Returns Result
│
▼
Final OutputThis is why nested subqueries are sometimes called:
Multi-Level SubqueriesBasic Nested Subquery Syntax
General syntax:
SELECT columns
FROM table_name
WHERE condition =
(
SELECT column
FROM table_name
WHERE condition =
(
SELECT expression
FROM table_name
)
);A subquery can contain another subquery, creating multiple levels.
Creating Sample Tables
Departments:
CREATE TABLE Departments (
DepartmentID INT,
DepartmentName VARCHAR(100),
Budget DECIMAL(12,2)
);Insert data:
INSERT INTO Departments VALUES
(1, 'IT', 1000000),
(2, 'HR', 500000),
(3, 'Finance', 1500000);Employees:
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
DepartmentID INT
);Insert data:
INSERT INTO Employees VALUES
(1, 'Rahul', 1),
(2, 'Priya', 3),
(3, 'Amit', 3),
(4, 'Neha', 2);First Nested Subquery Example
Query:
SELECT EmployeeName
FROM Employees
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Departments
WHERE Budget =
(
SELECT MAX(Budget)
FROM Departments
)
);Execution:
MAX(Budget)
=
1500000
↓
DepartmentID
=
3
↓
Employees in Department 3
↓
Priya
AmitResult:
| EmployeeName |
|---|
| ------------- |
| Priya |
| Amit |
Nested Subquery with Product Categories
Suppose:
Categories
| CategoryID | CategoryName |
|---|---|
| 1 | Electronics |
| 2 | Furniture |
Products
| ProductName | CategoryID |
|---|---|
| Laptop | 1 |
| Phone | 1 |
Query:
SELECT ProductName
FROM Products
WHERE CategoryID =
(
SELECT CategoryID
FROM Categories
WHERE CategoryName = 'Electronics'
);This already contains one subquery.
Adding another subquery creates a nested structure.
Nested Subquery with AVG()
Find employees earning more than the average salary of the department with the highest budget.
Example:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Departments
WHERE Budget =
(
SELECT MAX(Budget)
FROM Departments
)
)
);This demonstrates multiple levels of nesting.
Nested Subquery in WHERE Clause
The most common usage.
Example:
SELECT ProductName
FROM Products
WHERE CategoryID =
(
SELECT CategoryID
FROM Categories
WHERE ParentCategoryID =
(
SELECT CategoryID
FROM Categories
WHERE CategoryName = 'Electronics'
)
);Nested Subquery in FROM Clause
Example:
SELECT *
FROM
(
SELECT *
FROM
(
SELECT EmployeeName,
Salary
FROM Employees
) AS EmployeeData
) AS ResultData;Nested derived tables can be created.
Nested Subquery in SELECT Clause
Example:
SELECT
EmployeeName,
(
SELECT DepartmentName
FROM Departments
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Employees
WHERE EmployeeID = 1
)
) AS DepartmentName;Nested queries can also appear in SELECT statements.
Real-World Example: E-Commerce
Requirement:
Find products from the category
with the highest sales.Execution:
Find Highest Sales Category
↓
Find Category ID
↓
Find ProductsPerfect use case for nested subqueries.
Real-World Example: Banking
Requirement:
Find customers with balances
greater than the average balance
of the branch with the highest deposits.Multiple levels of calculations are required.
Nested subqueries simplify the solution.
Real-World Example: Education System
Requirement:
Find students enrolled in
the course with the highest enrollment.Process:
Find Highest Enrollment
↓
Find Course
↓
Find StudentsA nested subquery is an ideal solution.
Advantages of Nested Subqueries
Handles Complex Business Logic
Multiple operations can be performed within a single query.
Improves Query Flexibility
Results from one query can drive another query.
Reduces Temporary Tables
Many intermediate calculations can be performed directly.
Useful for Reporting
Commonly used in dashboards and analytics.
Disadvantages of Nested Subqueries
Reduced Readability
Deeply nested queries can become difficult to understand.
Harder Debugging
Errors may occur at multiple levels.
Performance Issues
Complex nested queries may perform poorly on large datasets.
Difficult Maintenance
Future developers may struggle to modify deeply nested queries.
Nested Subquery vs Simple Subquery
Simple Subquery:
SELECT *
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Only one subquery level.
Nested Subquery:
SELECT *
FROM Employees
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Departments
WHERE Budget =
(
SELECT MAX(Budget)
FROM Departments
)
);Multiple levels.
Nested Subquery vs JOIN
Nested Subquery:
Query Inside QueryJOIN:
Connect Tables DirectlyMany nested queries can be rewritten using joins.
Example:
INNER JOINmay provide better performance.
Performance Considerations
Nested Subqueries can be expensive because:
Inner Queries Execute Firstand may be evaluated repeatedly.
Performance depends on:
- Table Size
- Indexes
- Query Depth
- Database Engine
Large systems often optimize nested subqueries using:
Indexes
Query Rewriting
CTEs
JoinsCommon Errors
Excessive Nesting
Very deep nesting reduces readability.
Missing Parentheses
Wrong:
SELECT *
FROM Employees
WHERE DepartmentID =
SELECT DepartmentID
FROM Departments;Always use:
()Returning Multiple Values
Using:
=with a subquery returning multiple rows may cause errors.
Incorrect Data Types
Ensure all comparisons use compatible data types.
Best Practices
Keep Nesting Levels Reasonable
Avoid unnecessary complexity.
Test Queries Individually
Run inner queries first.
Use Meaningful Aliases
Improves readability.
Consider JOINs or CTEs
They may improve performance.
Index Frequently Used Columns
Improves execution speed.
Common Interview Questions
What is a Nested Subquery?
A subquery that contains another subquery.
In what order are nested subqueries executed?
From the innermost query outward.
Can nested subqueries be used in WHERE clauses?
Yes.
They are most commonly used there.
Can nested subqueries affect performance?
Yes.
Deeply nested queries may be slower on large datasets.
Can nested subqueries be replaced by JOINs?
In many cases, yes.
Summary
Nested Subqueries extend the power of standard subqueries by allowing multiple levels of querying. They help solve complex business problems by breaking them into smaller logical steps that SQL executes from the inside out.
In this lesson, you learned:
- What Nested Subqueries are
- Why they are important
- How they work
- Execution order
- Nested queries in WHERE
- Nested queries in FROM
- Nested queries in SELECT
- Real-world examples
- Advantages and disadvantages
- Performance considerations
- Best practices
Mastering Nested Subqueries is essential for handling advanced SQL reporting, analytics, and enterprise-level database operations.
Next Step
Continue to the next lesson:
Correlated Subquery →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Nested Subqueries.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, subqueries, nested
Related SQL Topics