SQL · Quick Reference

Lesson 10 — CTEs & WITH Clause Cheat Sheet

SQL

In one line: A CTE is declared before the main SELECT using WITH cte_name AS (...). Think of it as giving a subquery a name so you can reference it cleanly.

Key Ideas

1What Is a CTE?. A CTE is declared before the main SELECT using WITH cte_name AS (...). Think of it as giving a subquery a name so you can reference it cleanly.

2Multiple CTEs. You can chain multiple CTEs in one WITH block — separate them with commas. Each CTE can reference earlier ones defined in the same block.

3CTE vs Subquery. Both CTEs and subqueries produce temporary result sets — but they have different strengths:

4CTE with Aggregation. CTEs shine when you need to aggregate data in one step and filter on the result in the next — something WHERE cannot do directly.

5Recursive CTE. A recursive CTE references itself, making it ideal for hierarchical data like org charts, category trees, or bill-of-materials. It uses WITH RECURSIVE and always has a...

Code Examples

-- Basic CTE
WITH top_students AS (
  SELECT name, grade, age
  FROM students
  WHERE grade IN ('A', 'A+')
)
SELECT * FROM top_students ORDER BY age DESC;

WITH
  enrolled AS (
    SELECT DISTINCT student_id FROM enrollments
  ),
  unenrolled AS (
    SELECT id, name FROM students
    WHERE id NOT IN (SELECT student_id FROM enrolled)
  )
SELECT * FROM unenrolled;

WITH grade_stats AS (
  SELECT grade,
         COUNT(*)  AS total,
         AVG(age)  AS avg_age
  FROM students
  GROUP BY grade
),
above_avg AS (
  SELECT * FROM grade_stats WHERE avg_age > 16.5
)
SELECT * FROM above_avg ORDER BY total DESC;