📝 Common Table Expressions (CTEs)

Write cleaner, more readable SQL with temporary result sets

What Are CTEs?

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it as a temporary view that exists only for the duration of your query.

WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name;

❌ Without CTE (Subquery)

SELECT dept_name, avg_salary FROM ( SELECT department AS dept_name, AVG(salary) AS avg_salary FROM employees GROUP BY department ) AS dept_avg WHERE avg_salary > 60000;

⚠️ Harder to read, nested logic

✅ With CTE

WITH dept_avg AS ( SELECT department AS dept_name, AVG(salary) AS avg_salary FROM employees GROUP BY department ) SELECT dept_name, avg_salary FROM dept_avg WHERE avg_salary > 60000;

✓ Clear, readable, step-by-step logic

🎯 Basic CTE Example

Let's find employees earning more than their department's average salary:

WITH department_averages AS ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) SELECT e.name, e.department, e.salary, da.avg_salary, e.salary - da.avg_salary AS difference FROM employees e JOIN department_averages da ON e.department = da.department WHERE e.salary > da.avg_salary ORDER BY difference DESC;

Step 1: CTE creates temporary result

department avg_salary
Sales5000
Engineering6100
Marketing4800
⬇

Step 2: Main query uses CTE

name department salary avg_salary difference
Charlie Sales 5500 5000 +500
Frank Engineering 6500 6100 +400
Try This Example →

🔗 Multiple CTEs - Chain Your Logic

You can define multiple CTEs in a single query, separated by commas:

WITH sales_data AS ( SELECT employee_id, SUM(amount) AS total_sales FROM sales WHERE year = 2024 GROUP BY employee_id ), top_performers AS ( SELECT employee_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM sales_data ), employee_details AS ( SELECT e.employee_id, e.name, e.department, tp.total_sales, tp.sales_rank FROM employees e JOIN top_performers tp ON e.employee_id = tp.employee_id ) SELECT * FROM employee_details WHERE sales_rank <= 5;

💡 Benefits of Multiple CTEs

  • Break complex queries into logical steps
  • Each CTE can reference previous CTEs
  • Easier to debug - test each CTE independently
  • More maintainable code

🔄 Recursive CTEs - The Power Move

Recursive CTEs can reference themselves, making them perfect for hierarchical or graph data like organizational charts, file systems, or social networks.

WITH RECURSIVE employee_hierarchy AS ( -- Base case: Start with top-level managers SELECT employee_id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive case: Find employees of current level SELECT e.employee_id, e.name, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT REPEAT(' ', level - 1) || name AS hierarchy, level FROM employee_hierarchy ORDER BY level, name;

Input: Employee Table

employee_id name manager_id
1Alice (CEO)NULL
2Bob1
3Charlie1
4Diana2
5Eve2
6Frank3
⬇

Output: Hierarchical Structure

🔹 Level 1: Alice (CEO)
├─ Level 2: Bob
│ ├─ Level 3: Diana
│ └─ Level 3: Eve
└─ Level 2: Charlie
└─ Level 3: Frank

⚙️ How Recursive CTEs Work

1️⃣ Anchor Member (Base Case)

The initial query that returns the starting rows

SELECT * FROM employees WHERE manager_id IS NULL

Returns: Top-level managers

2️⃣ UNION ALL

Combines anchor and recursive members

Must use UNION ALL, not UNION (to avoid removing duplicates)

3️⃣ Recursive Member

References the CTE itself to get next level

SELECT e.* FROM employees e JOIN cte_name ON ...

Repeats until no more rows found

4️⃣ Termination

Stops when recursive member returns no rows

💡 Always ensure your recursion will terminate!

⚠️ Infinite Loop Protection

Add a safety limit to prevent infinite recursion:

WITH RECURSIVE cte_name AS ( ... WHERE level < 100 -- Maximum depth )

🌟 Real-World Recursive CTE Examples

📅 Generate Date Series

Create a list of dates between two values:

WITH RECURSIVE date_series AS ( SELECT '2024-01-01'::DATE AS date UNION ALL SELECT date + INTERVAL '1 day' FROM date_series WHERE date < '2024-01-31' ) SELECT * FROM date_series;
date
2024-01-01
2024-01-02
2024-01-03
...
2024-01-31

🗂️ File System Path Traversal

Find all files in a directory and subdirectories:

WITH RECURSIVE file_tree AS ( -- Start with root directory SELECT file_id, file_name, parent_id, file_name AS full_path FROM files WHERE parent_id IS NULL UNION ALL -- Add children SELECT f.file_id, f.file_name, f.parent_id, ft.full_path || '/' || f.file_name FROM files f JOIN file_tree ft ON f.parent_id = ft.file_id ) SELECT full_path FROM file_tree;

🌐 Social Network - Find Connections

Find all friends and friends-of-friends:

WITH RECURSIVE friend_network AS ( -- Direct friends SELECT user_id, friend_id, 1 AS degree FROM friendships WHERE user_id = 123 UNION -- Friends of friends (up to 3 degrees) SELECT fn.user_id, f.friend_id, fn.degree + 1 FROM friend_network fn JOIN friendships f ON fn.friend_id = f.user_id WHERE fn.degree < 3 ) SELECT DISTINCT friend_id, degree FROM friend_network ORDER BY degree, friend_id;

💼 When to Use CTEs

✅ Use CTEs When:

  • Query becomes hard to read
  • Need to reference same subquery multiple times
  • Breaking down complex logic step-by-step
  • Working with hierarchical data
  • Generating sequences or series

⚠️ Be Careful When:

  • Very large datasets (CTEs aren't always optimized)
  • Recursive queries without limits
  • Performance is critical (test vs subqueries)
  • Database doesn't support CTEs well

⚖️ CTEs vs Subqueries vs Temp Tables

Feature CTE Subquery Temp Table
Readability ✓ Excellent ✗ Poor ✓ Good
Reusability ✓ Within query ✗ No ✓ Across queries
Recursion ✓ Yes ✗ No ✗ No
Persistence Query only Query only ✓ Session
Indexing ✗ No ✗ No ✓ Yes
Performance Good Good ✓ Best (with index)

💡 Rule of Thumb

  • Use CTEs: For readability and one-time use in single query
  • Use Subqueries: For simple, one-off filtering
  • Use Temp Tables: When you need to reuse results across queries or add indexes

✨ Best Practices

✅ DO: Use Descriptive Names

WITH high_value_customers AS (...) -- ✓ Clear WITH cte1 AS (...) -- ✗ Unclear

✅ DO: Add Comments for Recursive CTEs

WITH RECURSIVE hierarchy AS ( -- Base: Start with root nodes SELECT ... UNION ALL -- Recursive: Add child nodes SELECT ... )

⚠️ DON'T: Create Infinite Loops

Always include a termination condition:

WHERE depth < max_depth -- ✓ Safe

⚠️ DON'T: Overuse CTEs

If your CTE is only used once and is simple, a subquery might be clearer. Use CTEs when they genuinely improve readability or enable recursion.

🚀 Ready to Master CTEs?

Practice these examples in our SQL editor!

Launch SQL Editor →