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.