SQL CTEs & Recursive Queries

◆SQL◆Databases◆Advanced◆Advanced🎯Free Tools

Introduction

Common Table Expressions (CTEs) use the WITH clause to create named temporary result sets. They improve query readability, enable code reuse within a query, and make complex logic easier to understand. Recursive CTEs allow you to traverse hierarchical data and perform iterative operations.

CTEs are evaluated once per query execution and can be referenced multiple times. They are not stored in the database — they exist only for the duration of the query.

Basic CTEs

A basic CTE creates a named temporary result set that you can reference in the main query. It is similar to a subquery but more readable and reusable.

basic-ctes.sql

SQL

1	-- Basic CTE: Named temporary result set
2	WITH active_users AS (
3	SELECT id, name, email
4	FROM users
5	WHERE status = 'active'
6	)
7	SELECT * FROM active_users;
8
9	-- CTE with JOIN
10	WITH user_orders AS (
11	SELECT
12	u.id AS user_id,
13	u.name,
14	o.id AS order_id,
15	o.total
16	FROM users u
17	JOIN orders o ON o.user_id = u.id
18	)
19	SELECT * FROM user_orders WHERE total > 100;
20
21	-- Multiple CTEs
22	WITH active_users AS (
23	SELECT id, name FROM users WHERE status = 'active'
24	),
25	recent_orders AS (
26	SELECT user_id, COUNT(*) AS order_count
27	FROM orders
28	WHERE created_at >= NOW() - INTERVAL '30 days'
29	GROUP BY user_id
30	)
31	SELECT
32	au.name,
33	COALESCE(ro.order_count, 0) AS recent_orders
34	FROM active_users au
35	LEFT JOIN recent_orders ro ON ro.user_id = au.id;
36
37	-- CTE with aggregation
38	WITH monthly_revenue AS (
39	SELECT
40	DATE_TRUNC('month', created_at) AS month,
41	SUM(total) AS revenue,
42	COUNT(*) AS order_count
43	FROM orders
44	WHERE status = 'completed'
45	GROUP BY DATE_TRUNC('month', created_at)
46	)
47	SELECT * FROM monthly_revenue ORDER BY month DESC;
48
49	-- CTE referenced multiple times
50	WITH order_stats AS (
51	SELECT
52	user_id,
53	COUNT(*) AS order_count,
54	SUM(total) AS total_spent
55	FROM orders
56	GROUP BY user_id
57	)
58	SELECT
59	u.name,
60	os.order_count,
61	os.total_spent,
62	os.total_spent / os.order_count AS avg_order_value
63	FROM users u
64	JOIN order_stats os ON os.user_id = u.id
65	WHERE os.order_count >= 3;

ℹ

info

CTEs improve readability by breaking complex queries into named logical steps. They are especially useful when you need to reference the same subquery multiple times.

CTE vs Subquery

cte-vs-subquery.sql

SQL

1	-- Subquery: Harder to read, can't reuse
2	SELECT
3	u.name,
4	(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count,
5	(SELECT SUM(total) FROM orders WHERE user_id = u.id) AS total_spent
6	FROM users u;
7
8	-- CTE: Cleaner, can reference multiple times
9	WITH order_summary AS (
10	SELECT
11	user_id,
12	COUNT(*) AS order_count,
13	SUM(total) AS total_spent
14	FROM orders
15	GROUP BY user_id
16	)
17	SELECT
18	u.name,
19	os.order_count,
20	os.total_spent
21	FROM users u
22	LEFT JOIN order_summary os ON os.user_id = u.id;
23
24	-- Subquery in FROM clause
25	SELECT
26	u.name,
27	os.order_count
28	FROM users u
29	LEFT JOIN (
30	SELECT user_id, COUNT(*) AS order_count
31	FROM orders
32	GROUP BY user_id
33	) os ON os.user_id = u.id;
34
35	-- CTE version (more readable)
36	WITH order_counts AS (
37	SELECT user_id, COUNT(*) AS order_count
38	FROM orders
39	GROUP BY user_id
40	)
41	SELECT
42	u.name,
43	oc.order_count
44	FROM users u
45	LEFT JOIN order_counts oc ON oc.user_id = u.id;
46
47	-- Complex subquery (nested)
48	SELECT
49	name,
50	department,
51	salary
52	FROM employees
53	WHERE salary > (
54	SELECT AVG(salary)
55	FROM employees
56	WHERE department = employees.department
57	);
58
59	-- CTE version (clearer intent)
60	WITH dept_avg AS (
61	SELECT department, AVG(salary) AS avg_salary
62	FROM employees
63	GROUP BY department
64	)
65	SELECT
66	e.name,
67	e.department,
68	e.salary
69	FROM employees e
70	JOIN dept_avg da ON da.department = e.department
71	WHERE e.salary > da.avg_salary;

Recursive CTEs

Recursive CTEs allow a CTE to reference itself, enabling hierarchical traversal and iterative operations. They consist of two parts: the anchor member (base case) and the recursive member.

recursive-ctes.sql

SQL

1	-- Recursive CTE: Employee hierarchy
2	WITH RECURSIVE org_tree AS (
3	-- Anchor member: Top-level employees (no manager)
4	SELECT
5	id,
6	name,
7	manager_id,
8	1 AS depth,
9	name AS path
10	FROM employees
11	WHERE manager_id IS NULL
12
13	UNION ALL
14
15	-- Recursive member: Employees with managers
16	SELECT
17	e.id,
18	e.name,
19	e.manager_id,
20	t.depth + 1,
21	t.path \|\| ' > ' \|\| e.name
22	FROM employees e
23	INNER JOIN org_tree t ON e.manager_id = t.id
24	)
25	SELECT * FROM org_tree ORDER BY path;
26
27	-- Recursive CTE: Generate number series
28	WITH RECURSIVE numbers AS (
29	SELECT 1 AS n
30	UNION ALL
31	SELECT n + 1 FROM numbers WHERE n < 10
32	)
33	SELECT * FROM numbers;
34
35	-- Recursive CTE: Date series
36	WITH RECURSIVE date_series AS (
37	SELECT DATE '2025-01-01' AS date
38	UNION ALL
39	SELECT date + INTERVAL '1 day'
40	FROM date_series
41	WHERE date < DATE '2025-01-31'
42	)
43	SELECT * FROM date_series;
44
45	-- Recursive CTE: Category tree
46	WITH RECURSIVE category_tree AS (
47	-- Base case: Root categories
48	SELECT
49	id,
50	name,
51	parent_id,
52	1 AS level,
53	ARRAY[name] AS path
54	FROM categories
55	WHERE parent_id IS NULL
56
57	UNION ALL
58
59	-- Recursive case: Child categories
60	SELECT
61	c.id,
62	c.name,
63	c.parent_id,
64	ct.level + 1,
65	ct.path \|\| c.name
66	FROM categories c
67	INNER JOIN category_tree ct ON c.parent_id = ct.id
68	)
69	SELECT
70	id,
71	name,
72	level,
73	path
74	FROM category_tree
75	ORDER BY path;
76
77	-- Recursive CTE with cycle detection
78	WITH RECURSIVE org_tree AS (
79	SELECT
80	id,
81	name,
82	manager_id,
83	1 AS depth,
84	ARRAY[id] AS visited,
85	false AS cycle
86	FROM employees
87	WHERE manager_id IS NULL
88
89	UNION ALL
90
91	SELECT
92	e.id,
93	e.name,
94	e.manager_id,
95	t.depth + 1,
96	t.visited \|\| e.id,
97	e.id = ANY(t.visited)
98	FROM employees e
99	INNER JOIN org_tree t ON e.manager_id = t.id
100	WHERE NOT t.cycle
101	)
102	SELECT * FROM org_tree WHERE NOT cycle;

⚠

warning

Recursive CTEs can cause infinite loops if there are cycles in your data. Always include a cycle detection mechanism or a maximum recursion depth limit.

CTE Materialization

PostgreSQL 12+ allows you to control whether a CTE is materialized (computed once and stored) or inlined (treated as a subquery). Materialized CTEs can improve performance when referenced multiple times, but may prevent query optimization.

cte-materialization.sql

SQL

1	-- MATERIALIZED: Compute once, store result
2	WITH order_summary AS MATERIALIZED (
3	SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
4	FROM orders
5	GROUP BY user_id
6	)
7	SELECT
8	u.name,
9	os.order_count,
10	os.total_spent
11	FROM users u
12	JOIN order_summary os ON os.user_id = u.id
13	JOIN order_stats os2 ON os2.user_id = u.id;
14
15	-- NOT MATERIALIZED: Treat as subquery (default in PostgreSQL 12+)
16	WITH order_summary AS NOT MATERIALIZED (
17	SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
18	FROM orders
19	GROUP BY user_id
20	)
21	SELECT
22	u.name,
23	os.order_count,
24	os.total_spent
25	FROM users u
26	JOIN order_summary os ON os.user_id = u.id;
27
28	-- When to use MATERIALIZED:
29	-- - CTE is referenced multiple times
30	-- - CTE is expensive to compute
31	-- - CTE has side effects (like random())
32
33	-- When to use NOT MATERIALIZED (default):
34	-- - CTE is referenced once
35	-- - CTE can benefit from query optimization
36	-- - CTE is cheap to compute
37
38	-- Example: Expensive computation referenced twice
39	WITH expensive_calc AS MATERIALIZED (
40	SELECT
41	user_id,
42	complex_function(data) AS result
43	FROM large_table
44	)
45	SELECT
46	a.result,
47	b.result
48	FROM expensive_calc a
49	JOIN expensive_calc b ON a.user_id = b.user_id;

Practical Examples

practical-examples.sql

SQL

1	-- 1. Top N per group using CTE
2	WITH ranked_products AS (
3	SELECT
4	*,
5	ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank
6	FROM products
7	)
8	SELECT * FROM ranked_products WHERE rank <= 3;
9
10	-- 2. Fibonacci sequence
11	WITH RECURSIVE fibonacci AS (
12	SELECT 0 AS n, 0 AS fib, 1 AS next_fib
13	UNION ALL
14	SELECT n + 1, next_fib, fib + next_fib
15	FROM fibonacci
16	WHERE n < 20
17	)
18	SELECT n, fib FROM fibonacci;
19
20	-- 3. Graph traversal (find all reachable nodes)
21	WITH RECURSIVE reachable AS (
22	-- Start from node A
23	SELECT id, name, 1 AS depth
24	FROM nodes
25	WHERE name = 'A'
26
27	UNION ALL
28
29	-- Traverse edges
30	SELECT n.id, n.name, r.depth + 1
31	FROM nodes n
32	JOIN edges e ON e.target_id = n.id
33	JOIN reachable r ON e.source_id = r.id
34	WHERE r.depth < 10 -- Limit depth
35	)
36	SELECT DISTINCT * FROM reachable;
37
38	-- 4. Moving average with CTE
39	WITH daily_sales AS (
40	SELECT
41	date,
42	SUM(amount) AS daily_total
43	FROM sales
44	GROUP BY date
45	),
46	moving_avg AS (
47	SELECT
48	date,
49	daily_total,
50	AVG(daily_total) OVER (
51	ORDER BY date
52	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
53	) AS moving_avg_7d
54	FROM daily_sales
55	)
56	SELECT * FROM moving_avg ORDER BY date;
57
58	-- 5. Hierarchical aggregation (rollup)
59	WITH RECURSIVE category_tree AS (
60	SELECT id, name, parent_id, 1 AS level
61	FROM categories
62	WHERE parent_id IS NULL
63
64	UNION ALL
65
66	SELECT c.id, c.name, c.parent_id, ct.level + 1
67	FROM categories c
68	JOIN category_tree ct ON c.parent_id = ct.id
69	),
70	category_sales AS (
71	SELECT
72	ct.id,
73	ct.name,
74	ct.level,
75	COALESCE(SUM(s.amount), 0) AS total_sales
76	FROM category_tree ct
77	LEFT JOIN products p ON p.category_id = ct.id
78	LEFT JOIN sales s ON s.product_id = p.id
79	GROUP BY ct.id, ct.name, ct.level
80	)
81	SELECT * FROM category_sales ORDER BY level, name;

Summary

Concept	Purpose	Example
WITH	Create named temporary result set	WITH cte AS (SELECT ...) SELECT * FROM cte
WITH RECURSIVE	Hierarchical/iterative queries	WITH RECURSIVE tree AS (...) SELECT * FROM tree
MATERIALIZED	Compute once, store result	WITH cte AS MATERIALIZED (...)
NOT MATERIALIZED	Treat as subquery	WITH cte AS NOT MATERIALIZED (...)
Anchor member	Base case in recursive CTE	SELECT ... WHERE parent_id IS NULL
Recursive member	Iterative part in recursive CTE	SELECT ... FROM ... JOIN cte ON ...

X GitHub YouTube Instagram Facebook Discord Slack Telegram Buy me a coffee Ko-fi Sponsor