Nested Subqueries

In the beginner course, you learned how to use a single subquery to filter or calculate values. Now we take it one step further by placing a subquery inside another subquery to solve more complex problems.

What is a Nested Subquery?

A nested subquery is a query inside another query.

This means:

• Inner query runs first
• Outer query uses its result
• Final result is returned

Think of it like solving a problem step by step.

1. Solve the smallest problem
2. Use that result to solve the next one

Basic Pattern

SELECT column_name
FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM another_table
    WHERE column_name = (
        SELECT value
        FROM third_table
        WHERE condition
    )
);

Example 1 — Students enrolled in the most recent course

Step 1: Find the highest course_id

Step 2: Find students enrolled in that course

SELECT s.student_id,
       s.first_name,
       s.last_name
FROM students s
WHERE s.student_id IN (
    SELECT e.student_id
    FROM enrollments e
    WHERE e.course_id = (
        SELECT MAX(course_id)
        FROM courses
    )
);

The inner query finds the latest course

The outer query finds students in that course

Example 2 — Courses offered in the same department as “Microeconomics”

SELECT course_id,
       course_name,
       department
FROM courses
WHERE department = (
    SELECT department
    FROM courses
    WHERE course_name = 'Microeconomics'
);

Inner query finds the department

Outer query finds all courses in that department

Example 3 — Instructors from Top Department

Step 1: Count courses per department

Step 2: Find the department with the highest count

Step 3: Find instructors in that department

SELECT i.first_name,
       i.last_name,
       i.department
FROM instructors i
WHERE i.department = (
    SELECT department
    FROM (
        SELECT department,
               COUNT(*) AS total_courses
        FROM courses
        GROUP BY department
        ORDER BY total_courses DESC
        LIMIT 1
    ) t
);

This is a nested subquery inside a derived table

Why Use Nested Subqueries?

• Break complex problems into steps
• Combine multiple logic levels
• Avoid multiple queries
• Useful for ranking, filtering, and comparisons

Nested Subqueries Summary

Feature	Description
Structure	Query inside another query
Execution	Inner query runs first
Best for	Multi-step filtering and logic
Strength	Solves complex problems cleanly

Simple Way to Think About It

A nested subquery is like asking:

“Find the answer… then use that answer to find something else.”

Practice Tasks

Task 1
Show students enrolled in the course with the lowest course_id.

Task 2
Show courses from the same department as ‘Social Theory’.

Task 3
Show instructors from the department with the most courses.