intro
Let's find out everything you need to know about the recursive CTE mechanism to master recursion in SQL statements.
Most developers are familiar with recursion—a programming technique where a function calls itself to solve a problem through smaller, repeated steps. This powerful strategy enables complex operations by breaking them down into simpler, manageable parts. A similar mechanism is available through a recursive CTE in SQL, too.
In this article, you will see what recursion-based CTEs are, how to use them in MySQL, PostgreSQL, and SQL Server, and when they are useful through practical examples.
Let’s dive in!
What Is a Recursive CTE?
A recursive CTE (Common Table Expression) is a special type of CTE that references itself, opening the door to recursive queries in SQL. In most DBMSs, that is the only supported mechanism for implementing recursion.
Recursive CTEs come in handy in all scenarios where recursion is useful, such as working with hierarchical, tree-structured, or self-referential data.
Recursive CTE SQL Syntax
An SQL recursive CTE must have two parts:
In simpler terms, a recursion-based CTE consists of a nonrecursive SELECT part followed by a recursive SELECT part. These two parts are usually connected by an SQL UNION or UNION ALL operator.
The SQL standard enforcers these constraints on a CTE recursive query:
Time to explore the syntax of recursive CTEs in MySQL, PostgreSQL, and SQL Server!
MySQL
A MySQL recursive CTE is defined as a recursive common table expression having a subquery that refers to its own name. This is the syntax supported by MySQL:
1
WITH RECURSIVE cte_name AS (
2
-- anchor member
3
SELECT column_1, column_2, ..., column_n
4
FROM table_name
5
[WHERE condition]
6
7
{UNION ALL | UNION [DISTINCT] }
8
9
-- recursive member
10
SELECT column_1, column_2, ..., column_n
11
FROM cte_name
12
[WHERE termination_condition]
13
)
14
SELECT * FROM cte_name;
Notes:
PostgreSQL
The syntax of a Postgres recursive CTE is mostly the same as that of MySQL.
Notes:
SQL Server
This is the recursive CTE SQL Server syntax:
1
WITH cte_name (column_1, column_2, ..., , column_n) AS (
2
-- anchor member
3
SELECT column_1, column_2, ..., column_n
4
FROM base_table
5
WHERE condition
6
7
UNION ALL
8
9
-- recursive member
10
SELECT column_1, column_2, ..., column_n
11
FROM cte_name
12
[WHERE termination_condition]
13
)
14
SELECT * FROM cte_name;
Notes:
CTE Recursive Query Examples
You now understand what a recursive CTE SQL statement is and how to write it. Still, you can grasp the power of its mechanism only through real-world examples. Time to dig into them!
Note: The queries below will be executed in DbVisualizer, a top-rated, feature-rich, visual database client. However, any other MySQL database client will work as well.
Example #1: Generate Sequences
SQL sequences are useful in various scenarios, from filling in gaps in datasets (e.g., missing days in a time series), to generating values for testing and debugging. Also, you can use them to implement MySQL counters.
This is how you can define a sequence with a CTE recursive query:
1
WITH RECURSIVE sequence AS (
2
SELECT 2 AS number
3
4
UNION ALL
5
6
SELECT number + 2
7
FROM sequence
8
WHERE number <= 100
9
)
10
SELECT * FROM sequence;
The above statement returns a sequence that starts from 2 and keeps adding values up to 100. In detail, it generates all even numbers from 2 to 100.
Verify that by executing it:
Example #2: Query Nested Data Structures
Assume that your application allows users to participate in threaded discussions with comments that can have nested replies. This data is stored in the comments table.
Given the ID of a comment, you may want to retrieve its entire hierarchy of sub-comments—essentially, extracting the complete content thread. Below is how you can use a recursive CTE to achieve that:
1
WITH RECURSIVE comment_hierarchy AS (
2
SELECT
3
id,
4
parent_id,
5
content,
6
author,
7
created_at,
8
1 AS level
9
FROM comments
10
WHERE id = 1
11
12
UNION ALL
13
14
SELECT
15
c.id,
16
c.parent_id,
17
c.content,
18
c.author,
19
c.created_at,
20
ch.level + 1 AS level
21
FROM comments c
22
JOIN comment_hierarchy ch ON c.parent_id = ch.id
23
)
24
SELECT content, author, created_at, level + 1 AS level
25
FROM comment_hierarchy
26
ORDER BY level, created_at;
In this example, our recursive CTE essentially has two SELECT queries looping through the entire comment hierarchy.
The result of this recursive CTE query will be as follows:
Thanks to the level column, the structure of the comment thread is easy to interpret. Without recursion, retrieving this data in a single query would be far more challenging, if not impossible.
Note that recursive CTE queries can become lengthy and complex. Thankfully, DbVisualizer Pro provides an SQL formatting feature that lets you neatly format queries with just a couple of clicks:
Formatting an SQL query in DbVisualizer
In this case, the formatted query will be formatted as follows:
1
WITH
2
RECURSIVE comment_hierarchy AS
3
( SELECT
4
id,
5
parent_id,
6
content,
7
author,
8
created_at,
9
1 AS LEVEL
10
FROM
11
comments
12
WHERE
13
id = 1
14
15
UNION ALL
16
17
SELECT
18
c.id,
19
c.parent_id,
20
c.content,
21
c.author,
22
c.created_at,
23
ch.level + 1 AS LEVEL
24
FROM
25
comments c
26
JOIN
27
comment_hierarchy ch
28
ON
29
c.parent_id = ch.id
30
)
31
SELECT
32
content,
33
author,
34
created_at,
35
LEVEL + 1 AS LEVEL
36
FROM
37
comment_hierarchy
38
ORDER BY
39
LEVEL,
40
created_at;
Much more readable, isn’t it? Evaluate DbVisualizer Pro with a 21-day free trial!
Example #3: Perform Cumulative Calculations
Suppose you have a sales table that records the amount of sales made each day of the year. Your goal is to retrieve a cumulative total of sales by date.
This is how you can achieve that through recursion:
1
WITH RECURSIVE cumulative_sales AS (
2
SELECT date, sales, sales AS cumulative_total
3
FROM sales
4
WHERE date = "2025-01-01"
5
6
UNION ALL
7
8
SELECT s.date, s.sales, cs.cumulative_total + s.sales
9
FROM sales s
10
JOIN cumulative_sales cs ON s.date = DATE_ADD(cs.date, INTERVAL 1 DAY)
11
)
12
SELECT date, sales, cumulative_total
13
FROM cumulative_sales;
The result will be:
As shown, the query starts from the first date and then accumulates the total sales in the cumulative_total column, day by day, through recursion. This allows you to see how sales add up over time.
Conclusion
In this guide, you learned about the SQL recursive CTE mechanism and how it works in MySQL, PostgreSQL, and SQL Server. You now understand how recursion works and how it can be applied to your queries.
SQL recursive queries can become complex, but they become easier to handle with a tool like DbVisualizer. This powerful database client supports multiple DBMS technologies and offers advanced features such as visual data exploration, query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is the SQL recursive CTE mechanism part of the SQL standard?
Yes, recursive CTEs are part of the SQL ISO/ANSI standard. At the same time, implementations may vary slightly across databases, with some differences in syntax, maximum recursion limits, and supported operations.
What is the difference between a regular CTE and a recursive CTE?
A regular CTE defines a temporary result set through in a single query that does not reference itself. On the contrary, a recursive CTE references itself within its definition to perform recursive logic.
How does an SQL server recursive CTE work?
SQL Server executes the anchor member to get the initial result set, while the recursive member references the CTE itself to expand the result set iteratively. The recursion continues until a termination condition is met. Each iteration combines new results with the previous ones, and the process repeats until a stopping point is reached, or the MAXRECURSION limit is hit.
How does a Postgres recursive CTE work?
This is how Postgre recursive CTE query evaluation works:
Why should I use a database client?
Adopting an SQL client like DbVisualizer allows you to visually deal with data in your databases. A robust SQL client offers a range of tools to simplify data management and analysis, no matter which DBMS you are using. These features enhance productivity, streamline workflows, and help you gain insights from your data more effectively.
