ADVANCED QUERYING INTERMEDIATE lesson-10

CTEs & WITH Clause

🕑 28 min 📚 Lesson 10 of 15 ✅ 5 quiz questions

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. CTEs make complex queries dramatically more readable — you break one large query into labeled, logical steps that you can reference multiple times.

Section 1

What 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.

SQL

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

The CTE top_students is defined once and queried in the final SELECT. It only exists for the duration of this single query — it is never stored on disk.

Section 2

Multiple 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.

SQL

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;

The second CTE unenrolled references the first CTE enrolled — this is much cleaner than deeply nested subqueries.

Section 3

CTE vs Subquery

Both CTEs and subqueries produce temporary result sets — but they have different strengths:

📄 CTE (WITH)

Named and self-documenting

Reusable multiple times in the same query

Easier to read and maintain

Supports recursion

Defined at the top, before main SELECT

📄 Subquery

Inline and anonymous

Must be repeated if used in multiple places

Can be harder to read when deeply nested

Good for simple one-off conditions

Correlated subqueries only work inline

Section 4

CTE 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.

SQL

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;

Section 5

Recursive 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 base case and a recursive part joined by UNION ALL.

SQL

WITH RECURSIVE org_chart AS (
  -- Base case: top-level employees (no manager)
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: add one level deeper each iteration
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

ℹ️

CTEs Are Temporary

CTEs only exist for the duration of the query — they are not stored in the database. Each time the query runs, the CTE is recalculated from scratch.

🏻 Knowledge Check — CTEs

1. What does CTE stand for?

Correct! CTE stands for Common Table Expression — a named temporary result set defined with the WITH keyword.

2. A CTE is defined using which keyword before SELECT?

Correct! CTEs use the WITH keyword: WITH cte_name AS ( ... ) SELECT ...

3. How does a CTE differ from a subquery?

Correct! CTEs are named and can be referenced multiple times in the same query; subqueries are anonymous and must be repeated.

4. A recursive CTE requires…

Correct! A recursive CTE needs a non-recursive base case and a recursive part, combined with UNION ALL.

5. After the query finishes, a CTE is stored in…

Correct! CTEs exist only for the duration of the query. They are not stored in the database after execution.

Finished this lesson?

Mark it complete to track your progress.