What is a Nested Subquery?
A nested subquery has subqueries inside subqueries - multiple levels!
Simple rule: Solve from inside out - innermost first!
How Nested Subquery Works
Basic Syntax
SELECT *
FROM table1
WHERE col > (
SELECT AGG(col)
FROM table2
WHERE col IN (
SELECT col FROM table3
)
);Example: Students Above Honor Average
SELECT name, age
FROM students
WHERE age > (
SELECT AVG(age)
FROM students
WHERE grade IN (
SELECT grade FROM honor_roll
)
);Step 1: Inner → honor grades (A, B) Step 2: Middle → AVG age of A,B students (22) Step 3: Outer → students older than 22
Nested Subquery Rules
- Solve inside-out - innermost query runs first
- Each level returns - value/list/table
- Keep it simple - max 2-3 levels recommended
Warning!
Deeply nested queries are hard to read. Consider using JOINs or CTEs instead for complex logic.
Try It Below
Practice with multi-level filtering!
What Comes Next
Next: Correlated Subqueries - subqueries that reference outer query!
Final Result
Nested query: Honor grades (A,B) → AVG age of A,B = 22 → Students older than 22
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 22 | B |
| 3 | Peter | 21 | C |
| 4 | Sarah | 23 | A |
| 5 | Tom | 24 | B |
5 rows
SQL Editor
Loading...
Filter by Grade
Find honor students (A or B). Try: SELECT name, age FROM students WHERE grade = 'A';
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 22 | B |
| 3 | Peter | 21 | C |
| 4 | Sarah | 23 | A |
| 5 | Tom | 24 | B |
5 rows
SQL Editor
Loading...