For those who work with data the CTE’s (Common Table Expression) syntax is quite popular, but a little explored feature are the Recursive CTEs. They are part of the ANSI SQL since 1999, but were adopted by the SQL Para quem trabalha com dados a sintaxe das CTEs (Common Table Expression) é bem popular, porém uma funcionalidade, pouco explorada são as CTEs Recursivas. Elas fazem parte do ANSI SQL desde 1999, but they were gradually adopted by SQL “dialects” over the years.

Examples of possible applications of this feature.

Tables where different hierarchies mix

Example: Suppose that a table contains records of employees up to leadership positions. It contains the ID of each employee and the ID of their immediate manager.

Let’s list all the employees who report to manager Jack (100):

** This is a very simple example, which could be solved in another way. Just to demonstrate the syntax.

 1WITH RECURSIVE dept_100 (id, name, pos) AS (
 2        SELECT
 3            id,
 4            name,
 5            pos
 6        FROM 
 7            funcionarios
 8        WHERE id = 100
 9
10        UNION ALL
11        SELECT 
12            e.id,
13            e.name,
14            e.pos
15        FROM 
16            employees AS e
17        INNER JOIN dept AS de 
18            ON e.id_mng = de.id
19    )
20SELECT * FROM dept_100

A SELECT is created initially, where the interaction will be based and the query after the UNION ALL will reference the CTE itself.

Another interesting application would be creating a calendar table. In the example below, I create a table with two columns: start_date (first day of the month) and end_date (last day of the month) recursively:

 1WITH RECURSIVE calendar AS (
 2    -- Base
 3    SELECT DATE '2023-01-01' AS start_date,
 4           LAST_DAY(DATE '2023-01-01') AS end_date
 5
 6    UNION ALL
 7
 8    -- Recursão: próximos meses
 9    SELECT DATEADD(MONTH, 1, start_date) AS start_date,
10           LAST_DAY(DATEADD(MONTH, 1, start_date)) AS end_date
11    FROM calendar
12    WHERE start_date < DATE '2025-12-01'
13)
14
15SELECT * FROM calendar;

In the BASE section, the start_date column was defined as 2023-01-01 and the end_date as the last day related to the same month as the first day, using LAST_DAY(). In the RECURSION section, a SELECT is performed on the CTE itself, always adding 1 month to both the start_date and end_date, recursively. The recursive loop ends when start_date reaches a value greater than 2025-12-01.

The resulting table will look something like: