SQL Notes
Learn how to write nested subqueries (subqueries within subqueries) in SQL, understand multiple levels of nesting, and solve complex data retrieval problems.
A subquery is a query inside another query. A nested subquery takes this further — it is a subquery inside a subquery, creating multiple levels of query logic. While this sounds complex, it is simply a way to break down a difficult question into smaller, layered steps.
Think of it like Russian nesting dolls. The innermost query runs first, its result feeds into the next level, which feeds into the outermost query. Each layer solves one part of the problem.
Nested subqueries are particularly useful in analytical and reporting scenarios where you need to answer complex business questions in a single query. Questions like "who are the top performers in the department with the highest average revenue" naturally decompose into multiple steps that can be expressed as nested levels. While JOINs and CTEs (Common Table Expressions) can often achieve the same result with better readability, understanding nested subqueries is essential for reading legacy code and for situations where the step-by-step decomposition makes the logic clearer.
Basic Structure
SELECT columns
FROM table
WHERE column operator (
SELECT column
FROM table
WHERE column operator (
SELECT column
FROM table
WHERE condition
)
);The innermost subquery executes first, then each outer layer uses the inner result.
Simple Example: Employees in the Highest-Paying Department
SELECT Name, Department, Salary
FROM Employees
WHERE Department = (
SELECT Department
FROM Employees
GROUP BY Department
HAVING AVG(Salary) = (
SELECT MAX(AvgSal)
FROM (
SELECT AVG(Salary) AS AvgSal
FROM Employees
GROUP BY Department
) AS DeptAverages
)
);How this executes (inside out):
- Innermost: Calculate average salary for each department
- Middle: Find the maximum of those averages
- Outer: Get employees in the department matching that maximum average
Two-Level Nesting: Products Above Category Average
Find products that cost more than the average price in their category, but only in categories where the average exceeds 1000:
SELECT ProductName, Category, Price
FROM Products
WHERE Price > (
SELECT AVG(Price)
FROM Products p2
WHERE p2.Category = Products.Category
)
AND Category IN (
SELECT Category
FROM Products
GROUP BY Category
HAVING AVG(Price) > 1000
);Nested Subquery in FROM Clause
You can nest subqueries as derived tables:
SELECT DeptName, HighEarnerCount
FROM (
SELECT Department AS DeptName, COUNT(*) AS HighEarnerCount
FROM Employees
WHERE Salary > (
SELECT AVG(Salary) FROM Employees
)
GROUP BY Department
) AS DeptStats
WHERE HighEarnerCount >= 3
ORDER BY HighEarnerCount DESC;The inner subquery finds the overall average salary. The middle query counts employees above average per department. The outer query filters departments with 3 or more high earners.
Real-World Example: E-Commerce — Best Customers
Find customers whose total spending exceeds the average spending of customers in the top-spending city:
SELECT c.CustomerID, c.Name, c.City, CustomerTotal.TotalSpent
FROM Customers c
JOIN (
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
) AS CustomerTotal ON c.CustomerID = CustomerTotal.CustomerID
WHERE CustomerTotal.TotalSpent > (
SELECT AVG(CityTotal)
FROM (
SELECT c2.City, SUM(o.TotalAmount) AS CityTotal
FROM Customers c2
JOIN Orders o ON c2.CustomerID = o.CustomerID
GROUP BY c2.City
ORDER BY CityTotal DESC
LIMIT 1
) AS TopCity
);Real-World Example: Student Rankings
Find students who scored above the class average in subjects where the class average is above 70:
SELECT s.Name, sub.SubjectName, e.Marks
FROM Students s
JOIN ExamResults e ON s.StudentID = e.StudentID
JOIN Subjects sub ON e.SubjectID = sub.SubjectID
WHERE e.Marks > (
SELECT AVG(e2.Marks)
FROM ExamResults e2
WHERE e2.SubjectID = e.SubjectID
)
AND e.SubjectID IN (
SELECT SubjectID
FROM ExamResults
GROUP BY SubjectID
HAVING AVG(Marks) > 70
)
ORDER BY sub.SubjectName, e.Marks DESC;Three-Level Nesting Example
Find employees who earn more than the average salary of the department that has the most employees:
SELECT Name, Department, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE Department = (
SELECT Department
FROM Employees
GROUP BY Department
ORDER BY COUNT(*) DESC
LIMIT 1
)
);Execution order:
- Level 3 (innermost): Find the department with most employees
- Level 2: Calculate average salary of that department
- Level 1 (outermost): Find employees earning above that average
Nested Subqueries with EXISTS
-- Find departments that have at least one employee
-- who earns more than every employee in the 'HR' department
SELECT DISTINCT d.DeptName
FROM Departments d
WHERE EXISTS (
SELECT 1 FROM Employees e
WHERE e.DeptID = d.DeptID
AND e.Salary > (
SELECT MAX(Salary)
FROM Employees
WHERE Department = 'HR'
)
);Nested Subqueries with IN
-- Products ordered by customers who have spent more than 50000 total
SELECT DISTINCT p.ProductName
FROM Products p
WHERE p.ProductID IN (
SELECT oi.ProductID
FROM OrderItems oi
WHERE oi.OrderID IN (
SELECT o.OrderID
FROM Orders o
WHERE o.CustomerID IN (
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 50000
)
)
);This four-level nesting finds: high-spending customers → their orders → products in those orders → product names.
Performance Considerations
Deeply nested subqueries can be slow because:
- The inner query may execute for every row of the outer query (correlated)
- Multiple levels multiply the execution cost
- The optimizer may not find the best plan for deep nesting
Rewriting with JOINs (Often Faster)
The nested subquery:
SELECT Name FROM Employees
WHERE DeptID IN (
SELECT DeptID FROM Departments
WHERE Location IN (
SELECT City FROM Offices WHERE Country = 'India'
)
);Can often be rewritten as:
SELECT e.Name
FROM Employees e
JOIN Departments d ON e.DeptID = d.DeptID
JOIN Offices o ON d.Location = o.City
WHERE o.Country = 'India';The JOIN version is typically more efficient and easier to read.
When to Use Nested Subqueries vs JOINs
| Scenario | Prefer |
|---|---|
| Simple existence checks | Nested subquery with EXISTS |
| Comparing against aggregates | Nested subquery |
| Retrieving data from multiple tables | JOIN |
| Multiple columns needed from related tables | JOIN |
| Step-by-step logical breakdown | Nested subquery (for clarity) |
| Performance-critical queries | JOIN (usually faster) |
Common Mistakes to Avoid
Mistake 1: Too many nesting levels. If you have more than 3 levels, the query becomes unreadable. Refactor using CTEs or JOINs.
Mistake 2: Correlated subqueries at every level. Each correlated level multiplies execution time. Use JOINs for better performance.
Mistake 3: Not testing inner queries independently. Always run each subquery separately first to verify it returns what you expect.
Mistake 4: Returning multiple rows where single value expected. If the outer query uses = operator, the subquery must return exactly one value.
-- FAILS if subquery returns multiple rows
WHERE Salary > (SELECT Salary FROM Employees WHERE Department = 'Sales')
-- Works: aggregate guarantees single value
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = 'Sales')Best Practices
- Build from inside out — write and test the innermost query first
- Limit nesting to 2-3 levels — deeper nesting becomes hard to maintain
- Consider CTEs (WITH clause) as a clearer alternative for complex logic
- Use JOINs when possible — they are often more efficient
- Always test subqueries independently before combining them
- Add comments explaining what each nesting level does
Nested subqueries are a powerful technique for solving multi-step problems in a single query. While they should not be your first choice for everything (JOINs and CTEs are often cleaner), they are essential knowledge for handling complex data retrieval scenarios that require layered logic.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Nested Subqueries in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, nested, nested subqueries in sql
Related SQL Complete Guide Topics