Using Common Table Expressions (CTEs)

Subqueries are powerful, but they can become hard to read when you nest them deeply. Common Table Expressions (CTEs) solve that problem by letting you name an intermediate result and reference it later in the same statement. In the University schema, CTEs make it easy to compose multi-step analysis—such as finding overloaded students, summarizing department activity, or generating synthetic semester timelines.

Learning Goals

• Define single and multiple CTEs with the University tables
• Reuse intermediate results to simplify complex joins and aggregates
• Build a recursive CTE to generate derived data
• Decide when a CTE is clearer than an inline subquery

CTE Basics

A CTE lives only for the duration of the query. It is defined with WITH and then referenced like a virtual table.

Basic CTE structure

WITH course_summary AS (
  SELECT dept_id,
         COUNT(*)    AS total_courses,
         AVG(credits) AS avg_credits
  FROM course
  GROUP BY dept_id
)
SELECT d.dept_name,
       cs.total_courses,
       cs.avg_credits
FROM course_summary AS cs
JOIN department AS d ON d.dept_id = cs.dept_id
ORDER BY cs.total_courses DESC;

Here the CTE computes course statistics per department. The outer query simply joins to department for friendly names.

Multiple CTEs

You can chain multiple CTEs by separating them with commas. This is great when each step builds on the previous one.

Multiple CTEs for enrollment analytics

WITH
spring_enrollments AS (
  SELECT e.student_id,
         e.course_id,
         e.semester
  FROM enrollment AS e
  WHERE e.semester = 'Spring 2025'
),
student_credit_load AS (
  SELECT se.student_id,
         SUM(c.credits) AS total_credits
  FROM spring_enrollments AS se
  JOIN course AS c ON c.course_id = se.course_id
  GROUP BY se.student_id
)
SELECT s.first_name || ' ' || s.last_name AS student_name,
       scl.total_credits
FROM student_credit_load AS scl
JOIN student AS s ON s.student_id = scl.student_id
WHERE scl.total_credits >= 7
ORDER BY scl.total_credits DESC;

The first CTE filters enrollments to a specific semester; the second calculates total credits; the final query lists students carrying heavy loads.

Recursive CTEs

Recursive CTEs reference themselves to generate rows repeatedly until a stopping condition is reached. Our schema is not hierarchical, but we can still use recursion to, for example, generate a list of future semesters for planning.

Generate the next six semesters

WITH RECURSIVE semester_plan AS (
  -- Anchor row
  SELECT 'Spring 2025' AS semester, 1 AS step
  UNION ALL
  -- Recursive term builder
  SELECT CASE
           WHEN semester LIKE 'Spring%' THEN 'Fall '  || SUBSTR(semester, 7)
           ELSE 'Spring ' || (CAST(SUBSTR(semester, 6) AS INTEGER) + 1)
         END AS semester,
         step + 1
  FROM semester_plan
  WHERE step < 6
)
SELECT semester
FROM semester_plan;

This produces alternating Spring/Fall labels, incrementing the calendar year each time Fall transitions back to Spring.

CTEs vs. Subqueries

Many subqueries can be rewritten as CTEs. Choose the style that communicates intent best.

High-demand departments

-- As a correlated subquery
SELECT d.dept_name
FROM department AS d
WHERE (
  SELECT COUNT(*)
  FROM enrollment AS e
  JOIN course AS c ON c.course_id = e.course_id
  WHERE c.dept_id = d.dept_id
    AND e.semester = 'Spring 2025'
) >= 8;

-- As a CTE
WITH spring_counts AS (
  SELECT c.dept_id,
         COUNT(*) AS enrollment_count
  FROM enrollment AS e
  JOIN course AS c ON c.course_id = e.course_id
  WHERE e.semester = 'Spring 2025'
  GROUP BY c.dept_id
)
SELECT d.dept_name
FROM spring_counts AS sc
JOIN department AS d ON d.dept_id = sc.dept_id
WHERE sc.enrollment_count >= 8;

Both versions answer the same question, but the CTE separates the counting logic from the final filter.

Common Pitfalls

• Missing RECURSIVE: Required when the CTE references itself.
• Infinite recursion: Always include a condition (like step < 6) to stop the recursive branch.
• Overuse: For simple lookups a plain query may be clearer; reserve CTEs for multi-step logic.
• Scope assumptions: CTEs exist only for the statement in which they are defined—they do not persist between commands.
• Performance surprises: Some databases materialize CTE results; others inline them. Test with your engine when performance matters.

Summary

• CTEs let you name intermediate results and keep complex queries readable.
• Multiple CTEs act like a recipe: each step feeds the next.
• Recursive CTEs can generate synthetic rows even when your base tables are flat.
• They are interchangeable with subqueries in many situations—choose whichever maximizes clarity.

Quiz

Show quiz

1. What keyword introduces a CTE?
   A) WITH
   B) CTE
   C) DEFINE
   D) TEMP

2. In the multi-CTE example, which step calculates total credits per student?
   A) spring_enrollments
   B) student_credit_load
   C) The final SELECT
   D) A correlated subquery

3. Which clause prevents the recursive semester generator from running forever?
   A) UNION ALL
   B) CASE
   C) WHERE step < 6
   D) JOIN

4. True or False: A CTE defined in one statement is available to reuse in the next statement you run.
   A) True
   B) False

---

Answers

1. A
2. B
3. C
4. B