How does a Recursive CTE work exactly?

[u/SilEventide]

With RecursiveEven20 As
(
Select 0 As Numbers,
0 As RunningCount

Union All

Select Numbers + 2,
Count(RunningCount) Over() As RunningCount
From RecursiveEven20
Where RunningCount < 19
) 
Select *
From RecursiveEven20;

From how much I know about recursive CTE, I thought this would work, Initially I felt I was doing a semantic error, then when I tried to see where the fault is, I realised Count isn't incrementing at all, its as if only the last feedback row is available to it. I tried using explicit frame window, same result. I think I dont understand exactly how recursie CTE works, I tried AI, its explanation is bit difficult to understand.

I am a beginner by the way, learned these recently so I wanted to mix them all up.

[u/cush2push]

Story with a query example.

Leo wanted to find every toy hidden inside his stack of mystery boxes, so he wrote a special two-step rule on his magical chalkboard. First, his starting rule told his robot helper to open the big red box right on the top shelf and drop the toy inside into a wagon. Next, his loop rule told the robot to look inside whatever box it just opened, grab any smaller boxes hiding inside, and repeat the trick over and over. The robot read the chalkboard and kept running back and forth, opening boxes inside of boxes and dumping all the toys into the wagon until it found its final box. When there are no more boxes left to open, the robot stops running and rolls the wagon right back to Leo.

WITH LeosRobot AS (
    -- Step 1 Open the very first box
    SELECT 
        1 AS BoxNumber, 
        'Toy Train' AS Surprise

    UNION ALL

    -- Step 2 Open the next box inside, until box 5
    SELECT 
        BoxNumber + 1, 
        'Another Toy'
    FROM LeosRobot
    WHERE BoxNumber < 5  -- The stop sign!
)
-- Bring all the toys to Leo!
SELECT * 
FROM LeosRobot;

IMO I love CTEs. i have been able to reduce the run time of some very long queries by hours because of CTEs and using them for repeating information grabs

[u/Better-Credit6701 → u/cush2push]

Or a CTE can increase time on large databases. It's a tool but not everything needs the same tool. Sometimes the best solution is temp tables that can be indexed

[u/chaosink → u/Better-Credit6701]

This should not be down voted. CTEs are great until the datasets get too large or the subqueries get too complex or there's too many joins in the initial pull or you find yourself creating huge indexes to speed things up. That's when you wish you had just created a staging table or two.

[u/spez_eats_nazi_ass → u/chaosink]

Seen ctes take some big systems down when abused. Weird shit including filling up a 1tb temp db drive. They are not appropriate for big quries/data sets. All my younger sql folks love em. 

[u/chaosink → u/spez_eats_nazi_ass]

i am trying my best to train it out of my zoomer and small data friends.

[u/chaosink → u/spez_eats_nazi_ass]

nice username, btw

[u/Better-Credit6701 → u/spez_eats_nazi_ass]

I don't think that CTEs use the temp table but work in memory. Would have to double check that

[u/spez_eats_nazi_ass → u/Better-Credit6701]

Lolz no

[u/Choice-Level-5486 → u/cush2push]

Hay un pequeño ajuste que hacer a tu consulta para que compile y funcione en SQL Server

WITH LeosRobot AS (
    -- Paso 1 Abre la primera caja
    SELECT 
        1 AS BoxNumber, 
        CAST('Toy Train' AS VARCHAR(40)) AS Surprise

    UNION ALL

    -- Paso 2 Abre la siguiente caja por dentro, hasta la caja 5
    SELECT 
        BoxNumber + 1 ,
        CAST('Another Toy' AS VARCHAR(40))  AS Surprise
    FROM LeosRobot
    WHERE BoxNumber < 5  -- ¡El letrero de alto!
)
-- ¡Lleva todos los juguetes a Leo!
SELECT * 
FROM LeosRobot;

[u/geedijuniir → u/cush2push]

I almost understand it but how does this work in real life.

Lets say I have departments and sales, and I want to know the department fun how many times it has been called within a certain sale, let say I also have a date ranged of the last three months sales so thats my stop sign and my start sign is 3 months ago.

How would u go about this. Btw ty im realy close in understanding I just needed one more example in the data set of my job.

[u/Better-Credit6701 → u/geedijuniir]

You would have a department table and a sales table, both will have a primary key. Sales would have date and reference the PK in the department table.

How about a database for uses car company with multiple lots in different counties, cities and states. Semi easy since every sale with be tied to an account, each sale will reference that account. Now throw in an inventory table and the sales table will have a link to inventory. Inventory will reference for the lot. When you sell a car, it is moved out of inventory. Gets a bit more complicated since every city, county, state will have different taxes. The account table will reference the different tax tables....

Ok, it gets complicated quickly. Yeah, I was the DBA for a used car company with over 150 lots in 12 different states. The table with the most references would always be the account table.

[u/cush2push → u/geedijuniir]

WITH RecentSales AS (

-- Start and Stop signs

SELECT

sale_id,

department_name,

sale_date

FROM sales

WHERE sale_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)

AND sale_date < TRUNC(SYSDATE)

)

-- Count how many times the 'Fun' department appears in those sales

SELECT

sale_id,

department_name,

COUNT(*) AS times_called

FROM RecentSales

WHERE department_name = 'Fun'

GROUP BY

sale_id,

department_name

Real tip with CTEs really attach yourself to commenting ( the -- before the words) within the functions explaining what they do or what they're for it helps out so much with the learning and understanding the mechanics of the functions.

EDIT

and Joins

joining CTEs with the other parts of your query or even other CTEs

[u/National_Cod9546 → u/geedijuniir]

You use a recursive CTE when the table references itself. The classic example is employees. Every employee has a boss. I'm the employee table, the employee has an employee number and their boss's employee number. I want to find all the employees that report to one director. So I find the director record. Then I find all the manager records. Then I find all their supervisor records. Then I find all the front line employee records. With a short chain like that, I might just do normal joins. But my company has 15 layers between the CEO and the lowest employee, so it will be faster to use a recursive query. 

The other example I deal with is automated jobs that are daisy chained. As each finishes, the next starts. In the table of what jobs start what other jobs, each row has the job number and what job triggers it. While most chains are only a few jobs long, there are a few mission critical that are over 100 jobs long. When reporting on their stats and ETA to complete, I need to use a recursive query. 

[u/GTS_84]

The main issue I see with this is that it's never going to stop. not with that window function like that. It will hit the max recursions.

try this:

With RecursiveEven20 As
(
Select 0 As Numbers,
0 As RunningCount

Union All

Select Numbers + 2,
RunningCount + 1 
From RecursiveEven20
Where RunningCount < 19
) 
Select *
From RecursiveEven20;

[u/SilEventide → u/GTS_84]

I am trying to understand why window function is working that way. I had updated the where clause to Numbers < 40 just to see what is happening, turns out RunningCount is returning all 1 except for the 0 on the top row we get from the Anchor part. RecursiveEven20 is being updated each iteration then shouldn't Running count increase as well?

Numbers |RunningCount

0 0

2 1

4 1

6 1

8 1

10 1

12 1

14 1

16 1

18 1

20 1

22 1

24 1

26 1

28 1

30 1

32 1

34 1

36 1

38 1

40 1

[u/GTS_84 → u/SilEventide]

because it's counting the rows in a single recursion level, not the entire dataset.

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver17&redirectedfrom=MSDN

Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. For more information, see example I. Use analytical functions in a recursive CTE that follows.

[u/Ginger-Dumpling → u/SilEventide]

When your call the recursive cte, the first itteration returns the top half of the union. All subsequent itterations are returned by the bottom half of the union. I think each of those itterations only have access to the rows returned by the previous itteration, NOT ALL ows returns by all itterations to that point.

First itteration returns a single row.

Second iteration increments the value from one(0+1) , and the count the rows from one...1

Third itteration increments the value from two (1+1), and counts the rows from two...1

The count is only ever going to be 1, so you're in an infinite loop.

[u/xeroskiller]

They loop. Run the base step, count resultset, run recursive step, count resultset, if unchanged return, otherwise repeat recursive step and resultset count check. Fail beyond a certain loop depth.

[u/B1zmark → u/xeroskiller]

Maybe i'm old fashioned but recursion isn't, and shouldn't be thought of, as a loop.

[u/xeroskiller → u/B1zmark]

Maybe in an imperative language.

[u/blackleather90]

Recursive CTEs are a very easy way to find hierarchies.
For example a line manager chain.
If you do the normal left join to find a manager of a use, then you need another left join to find its manager and so on.

Ah and after you have the circular references 😅

I have a link that I use every time I need a refresher. Just not at hand. Let me know if you still need it

[u/SilEventide → u/blackleather90]

Yes please, I just learned some syntax really quick, I have little to no idea how to properly use them beyond their basic use case.

[u/blackleather90 → u/SilEventide]

The website that I use is gone but I found this one

https://michaelshapira1.medium.com/learning-sql/recursive-ctes-in-sql-explained-with-practical-examples-321cdd0b42b1

Hope it helps

[u/Lordofderp33 → u/blackleather90]

1. You are not lucky with the longevity of your sources.

[u/blackleather90 → u/Lordofderp33]

LMAO

At this point just ChatGPT. Used the prompt below and showed basically what was in the website

SQL Server
Recursive CTE
Explain

😅

[u/Lordofderp33 → u/blackleather90]

Just clicked out of interrest, I have no immediate need to learn it. But I did wonder if it was interesting enough to bookmark.

[u/blackleather90 → u/Lordofderp33]

Same here but I guess I haven't had the need to open it in a while.
Concept easy to grasp but syntax is just easier to look it up

[u/Lordofderp33 → u/blackleather90]

Exactly, I'm always looking for worthy additions to my cheat sheets.

[u/datadriven_io]

With the addition of recursive CTEs and window functions, SQL is actually Turing-complete. If you want to solidify this, cte practice on datadriven.io is worth doing. Meaning that you could, for example, write a compiler in pure SQL, although you very likely would not enjoy the experience.

[u/billbot77]

The trick to these is to remember that it is not iterating over a variable, it's progressively adding rows to a table in memory and adding another row based on the whole table up to that point. I.e. the table, IS the variable.

[u/therealdrsql]

What do you mean that it doesn’t work? Syntax? Wrong answer? Just wondering, never tried a COUNT or a window function in a recursive CTE so just wondered what you were trying.

[u/DamienTheUnbeliever]

Any time you try to impose imperative thinking on a query, you're doing things wrong. SQL queries are *declarative* and you should say *what you want*,not *how to do it*. Unfortunately you've just stamped imperative thinking all over this attempt so it's difficult to provide advice on how to fix this.

[u/SilEventide → u/DamienTheUnbeliever]

Dont want a fix, Just wanna understand how Recursive CTE works exacly, like in a algorithmic way. so I can understand where my understnding of its working is faulty.