Database.Middle.What are CTEs (Common Table Expressions)?

CTEs (Common Table Expressions) are a way to write temporary, reusable result sets (like named subqueries) in SQL, using the WITH keyword. They’re especially useful for making complex queries more readable, modular, and recursive.

📌 Basic Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Think of a CTE as a temporary view that only exists during the execution of the main query.

---

🧾 Example 1: Simple CTE

WITH recent_orders AS (
  SELECT * FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) 
FROM recent_orders
GROUP BY customer_id;

✅ What it does:

• Creates a CTE called recent_orders
• Then uses it as if it’s a table in the main SELECT

✅ Benefits of CTEs

Feature	Description
🔍 Readability	Breaks large queries into understandable parts
🧩 Reusability	Can refer to the same logic multiple times
🧠 Recursion support	Enables recursive queries like hierarchies
🔄 Avoids duplication	Don’t repeat long subqueries

🧾 Example 2: Recursive CTE (Hierarchy)

Suppose you have an employees table with a manager_id column:

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL  -- start from the CEO

  UNION ALL

  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;

✅ This will return the full hierarchy of employees, with levels!

🆚 CTE vs Subquery vs Temp Table

Feature	CTE	Subquery	Temp Table
Readability	✅ Clear names	❌ Can get messy	✅ Clear names
Reusability	✅ Yes	❌ Not reusable	✅ Yes
Performance	🔄 Usually similar	✅ Sometimes better	✅ Good for huge datasets
Recursive support	✅ Yes	❌ No	❌ No

---

🧠 Summary

• A CTE is a named temporary result set used in a query.
• Declared with the WITH clause
• Can be recursive for things like org charts or tree traversal
• Improves query clarity and modularity