Subquery vs CTE vs Derived Table Comparison

In the beginner course, you learned how to retrieve, filter, and summarize data using SELECT, WHERE, and GROUP BY. Now we compare three ways to structure queries when the logic becomes more complex.

A subquery, derived table, and CTE all allow you to break a problem into steps. The difference is where the query is written and how easy it is to read and reuse.

This pattern is perfect for:

• Breaking complex logic into smaller steps
• Reusing computed results
• Improving query readability
• Structuring multi-step analysis

Basic Patterns

Subquery (inside WHERE)

SELECT column
FROM table_name
WHERE column IN (
    SELECT column
    FROM other_table
);

Derived Table (inside FROM)

SELECT *
FROM (
    SELECT column
    FROM table_name
) AS t;

CTE (at the top)

WITH name AS (
    SELECT column
    FROM table_name
)
SELECT *
FROM name;

Examples

Example 1 — Students who have enrollments (Subquery)

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
);

This filters students using a subquery inside WHERE.

Example 2 — Students who have enrollments (Derived Table)

SELECT s.student_id,
       s.first_name,
       s.last_name
FROM students s
JOIN (
    SELECT DISTINCT student_id
    FROM enrollments
) AS t
ON s.student_id = t.student_id;

This creates a temporary table, then joins it.

Example 3 — Students who have enrollments (CTE)

WITH EnrolledStudents AS (
    SELECT DISTINCT student_id
    FROM enrollments
)
SELECT s.student_id,
       s.first_name,
       s.last_name
FROM students s
JOIN EnrolledStudents e
ON s.student_id = e.student_id;

This creates a named result first, then uses it.

Why Use Each Approach?

Subquery

• Simple filtering
• Quick logic inside WHERE
• Best for one-step conditions

Derived Table

• Intermediate results inside FROM
• Useful for grouping and counting first
• Keeps logic inside the main query

CTE

• Breaks queries into clear steps
• Improves readability
• Useful for multi-step problems

Subquery vs Derived Table vs CTE Summary

Feature	Subquery	Derived Table	CTE
Location	WHERE / SELECT	FROM	Top of query
Readability	Simple queries	Medium	High
Reuse	No	No	Yes
Best for	Filtering	Intermediate steps	Multi-step logic

A subquery is like asking a quick question inside your query.
A derived table is like creating a temporary table in the middle.
A CTE is like writing your steps at the top before solving the problem.

Practice Tasks (Your Turn!)

Task 1: Courses Table
Show courses that appear in the enrollments table using a subquery.

Task 2: Courses Table
Solve the same problem using a derived table.

Task 3: Courses Table
Solve the same problem using a CTE.