Subqueries in FROM

Overview

Subqueries in FROM — Derived Tables

A subquery in the FROM clause creates a temporary table (also called a derived table).

You can then query that temporary table as if it were a real table.

This pattern is perfect for:

• Multi-step logic
• Aggregations that need further filtering
• Breaking complex queries into readable parts
• Creating intermediate result sets
• Replacing overly complex joins

Derived tables are one of the most important intermediate SQL skills.

---

The Basic Pattern

SELECT columns
FROM (
    SELECT ...
    FROM table_name
    WHERE ...
) AS derived_table;
  

The subquery must have an alias.

---

Why Use Derived Tables?

• They make complex queries readable
• They allow you to filter on aggregated results
• They let you reuse logic inside a larger query
• They behave like temporary views

---

Examples

Example 1: Find courses with more than 5 students

Step 1: Count students per course
Step 2: Filter the results

SELECT *
FROM (
    SELECT e.course_id,
           COUNT(*) AS NumStudents
    FROM enrollments e
    GROUP BY e.course_id
) AS course_counts
WHERE NumStudents > 5;
  

This is the classic “aggregate then filter” pattern.

Example 2: Show students with their total enrollments

SELECT s.student_id,
       s.first_name,
       s.last_name,
       t.TotalEnrollments
FROM students s
JOIN (
    SELECT student_id,
           COUNT(*) AS TotalEnrollments
    FROM enrollments
    GROUP BY student_id
) AS t
    ON s.student_id = t.student_id;
  

This avoids repeating the COUNT logic.

Example 3: Average grade per course, then show only courses above the overall average

Step 1: Compute average grade per course
Step 2: Compare each course to the global average

SELECT c.course_id,
       c.course_name,
       d.avg_grade
FROM courses c
JOIN (
    SELECT course_id,
           AVG(grade) AS avg_grade
    FROM enrollments
    GROUP BY course_id
) AS d
    ON c.course_id = d.course_id
WHERE d.avg_grade > (
    SELECT AVG(grade)
    FROM enrollments
);
  

A clean multi-step analysis.

Example 4: Count courses per department, then join to instructors

SELECT i.first_name,
       i.last_name,
       i.department,
       d.NumCourses
FROM instructors i
LEFT JOIN (
    SELECT department,
           COUNT(*) AS NumCourses
    FROM courses
    GROUP BY department
) AS d
    ON i.department = d.department;
  

This adds department-level metrics to each instructor.

---

Derived Tables — Summary

Feature	Description
Purpose	Create a temporary table inside a query
Must have	An alias
Best for	Multi-step logic, aggregated filtering
Strength	Makes complex queries readable and modular

---

A derived table is like taking notes during class.
You write something down once… then use it wherever you need it

---

Practice Tasks (Your Turn!)

Task 1: Courses Table

Create a derived table that counts the number of students per course, then display all courses that have at least one students enrolled.

Task 2: Students Table

Create a derived table that counts enrollments per student, then join it back to students.

Task 3: Instructors Table

Create a derived table that counts courses per department, then join it to instructors.