Common Table Expressions (CTEs)

WITH clauses name intermediate result sets—readable multi-step SQL and recursive trees/graphs.

Non-recursive CTE

WITH big_orders AS (
  SELECT * FROM orders WHERE total > 500
)
SELECT c.name, b.total
FROM big_orders b
JOIN customers c ON c.id = b.customer_id;

Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).

Recursive CTE

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Always include a termination condition—runaway recursion exhausts resources.

CTE materialization note

Postgres may inline or materialize CTEs depending on version and settings—use EXPLAIN to verify performance on hot paths.

Important interview questions and answers

1. Q: CTE vs subquery?
   A: CTEs improve readability; optimizer may treat similarly—profile hot queries.
2. Q: Recursive CTE parts?
   A: Anchor member UNION ALL recursive member referencing the CTE name.

Self-check

1. What keyword starts a recursive CTE?
2. Why name intermediate steps with WITH?

Tip: Name steps in WITH clauses—readability wins in interviews and code review.