Chapter 10: Subqueries & Nested Queries
A subquery (also called a nested query) is simply a query inside another query. It’s like asking a small question to get an answer, and then using that answer to ask the big question.
Subqueries can appear in three main places:
- In SELECT (as a calculated column)
- In FROM (as a temporary table)
- In WHERE (most common – to filter rows)
We’ll also learn correlated subqueries (the subquery depends on the outer query), EXISTS / NOT EXISTS, and ANY / ALL.
First – let’s make sure our data is ready We’ll use the same students, courses, and enrollments tables from Chapter 9. If you haven’t run the setup yet, quickly do this:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- (Assuming you already have students table) -- Quick check: add a fees_paid column to enrollments for better examples ALTER TABLE enrollments ADD COLUMN fees_paid DECIMAL(10,2) DEFAULT 0.00; -- Update some fees paid UPDATE enrollments SET fees_paid = 6000.00 WHERE enrollment_id = 1; -- Priya Maths full UPDATE enrollments SET fees_paid = 3000.00 WHERE enrollment_id = 2; -- Priya Science half UPDATE enrollments SET fees_paid = 4500.00 WHERE enrollment_id = 3; -- Rahul English full UPDATE enrollments SET fees_paid = 4000.00 WHERE enrollment_id = 5; -- Kavya Hindi full |
Now let’s dive in!
1. Subqueries in WHERE Clause (Most Common)
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT columns FROM table WHERE column operator (subquery); |
Example 1 – Students who enrolled in Mathematics
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT full_name, gender, join_date FROM students WHERE student_id IN ( SELECT student_id FROM enrollments WHERE course_id = ( SELECT course_id FROM courses WHERE course_name = 'Mathematics' ) ); |
Step-by-step what happens:
- Innermost: Finds course_id of ‘Mathematics’ → say 1
- Middle: Finds all student_ids enrolled in course_id 1
- Outer: Shows only those students
Example 2 – Students who paid full fees for at least one course
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT full_name FROM students WHERE student_id IN ( SELECT student_id FROM enrollments WHERE fees_paid = ( SELECT fees FROM courses WHERE courses.course_id = enrollments.course_id ) ); |
2. Subqueries in SELECT Clause (Calculated Column)
Syntax:
|
0 1 2 3 4 5 6 7 |
SELECT column, (subquery) AS alias, ... FROM table; |
Example – Show each student’s total courses enrolled
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT full_name, ( SELECT COUNT(*) FROM enrollments WHERE enrollments.student_id = students.student_id ) AS 'Total Courses Enrolled' FROM students ORDER BY 'Total Courses Enrolled' DESC; |
Result example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
+---------------+-----------------------+ | full_name | Total Courses Enrolled| +---------------+-----------------------+ | Priya Sharma | 2 | | Sneha Joshi | 1 | | Rahul Patel | 1 | | ... | 0 | ← students with no courses +---------------+-----------------------+ |
3. Subqueries in FROM Clause (Derived Table / Temporary Table)
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT columns FROM (subquery) AS alias WHERE ... |
Example – Find courses with more than 1 student enrolled
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT course_name, enrolled_count FROM ( SELECT c.course_name, COUNT(e.student_id) AS enrolled_count FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id GROUP BY c.course_name ) AS course_stats WHERE enrolled_count > 1 ORDER BY enrolled_count DESC; |
This is like creating a temporary table called course_stats and then filtering it.
4. Correlated Subqueries
(The subquery depends on the outer query – runs once per outer row)
Example – Students who enrolled in more than 1 course
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT full_name, join_date FROM students s WHERE ( SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.student_id -- correlated! ) > 1; |
Result: Only Priya Sharma (because she enrolled in 2 courses)
Notice: The subquery uses s.student_id from the outer query → that’s what makes it correlated.
5. EXISTS / NOT EXISTS
(Checks if subquery returns at least one row – very efficient!)
Syntax:
|
0 1 2 3 4 5 6 7 |
WHERE EXISTS (subquery) WHERE NOT EXISTS (subquery) |
Example – Students who are enrolled in at least one course
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT full_name FROM students s WHERE EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id ) ORDER BY full_name; |
Opposite – Students who have NOT enrolled in any course
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT full_name FROM students s WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id ); |
Result: Rohan Mehta, Meera Desai, Arjun Reddy (and any others with no enrollments)
Why use EXISTS instead of IN?
- EXISTS stops as soon as it finds one match → faster for large tables
- Works perfectly with correlated subqueries
6. ANY / ALL
(Compares a value with any or all values returned by subquery)
| Operator | Meaning | Example Meaning |
|---|---|---|
| ANY | At least one value matches | Greater than ANY → greater than at least one |
| ALL | Matches all values | Greater than ALL → greater than every one |
Example – Students who enrolled in a course that costs more than ANY course fees
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT full_name FROM students s WHERE EXISTS ( SELECT 1 FROM enrollments e JOIN courses c ON e.course_id = c.course_id WHERE e.student_id = s.student_id AND c.fees > ANY (SELECT fees FROM courses) ); |
Better example – Students enrolled in the most expensive course
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT s.full_name, c.course_name, c.fees FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id WHERE c.fees = (SELECT MAX(fees) FROM courses); |
Using ALL – Courses more expensive than ALL other courses (i.e., the most expensive)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT course_name, fees FROM courses WHERE fees > ALL ( SELECT fees FROM courses WHERE course_id != courses.course_id ); |
That’s it for Chapter 10! 🎉 You can now write very advanced, nested queries that answer complex questions!
Homework for today (do it right now – very important!) Run these 5 queries and paste the output of each (at least first few rows):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
-- 1. Students who enrolled in more than 1 course (correlated subquery) SELECT full_name FROM students s WHERE ( SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.student_id ) > 1; -- 2. Students who have NOT enrolled in any course (NOT EXISTS) SELECT full_name FROM students s WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id ); -- 3. Total courses enrolled per student (subquery in SELECT) SELECT full_name, (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.student_id) AS total_courses FROM students s ORDER BY total_courses DESC; -- 4. Courses with at least 2 students enrolled (subquery in FROM + HAVING) SELECT course_name, enrolled_count FROM ( SELECT c.course_name, COUNT(e.student_id) AS enrolled_count FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id GROUP BY c.course_name ) AS stats WHERE enrolled_count >= 2; -- 5. Students enrolled in Mathematics OR Science (using ANY) SELECT DISTINCT s.full_name FROM students s JOIN enrollments e ON s.student_id = e.student_id WHERE e.course_id = ANY ( SELECT course_id FROM courses WHERE course_name IN ('Mathematics', 'Science') ); |
