Now that we've learned about CTEs, we get to learn about recursive CTEs. We're developers. We love recursive stuff. So recursive CTEs are like c t e's except that they reference themselves over and over and over as they're building up the table itself. Let me show you a few very basic examples and then I'm gonna show you 1 that is, probably useful for many of the domains that you work within.

With numbers, numbers as, this is ACTE. And then we can do select star from numbers. We're missing this part and for a recursive CTE you would say with recursive numbers as, this is our table name, this could be anything. We just decided numbers is a good name. Now the recursive CTE has 2 parts, the initial condition and the recursive condition.

So we are gonna say select 1 as n. That is our initial condition. In a recursive CTE it's very important that you do union all because if you don't do union all it has to keep track of every row that it's ever generated to make sure that there's not a duplicate. Whereas, if you do a union all, it really doesn't keep track of every single row ever generated. Entering into the recursive condition, we can reference the very table that we are building up which is where it starts to get mind bendy.

So we'll do select n plus 1. So that comes from the row before it. Select n plus 1 from the very table that we're building where in is less than 10. And now if we run that, you'll see we have generated from thin air, from computers, we've generated 10 numbers. So you have to have a condition that stops it.

Otherwise, it's just gonna well, I shouldn't have done that. It's just gonna keep going and I'm probably gonna have to quit this and come back, so hang tight. Okay. We're back. Make sure you add your own limit condition.

So we can do a limit 10 or we can say some sort of other condition wherein is less than 10. Whatever causes the recursive condition to stop, make sure you add that. Don't be like me. Make sure you add that. It doesn't just have to be numbers.

We could say dates and, let's do select 2020 24. This one's actually pretty useful because you can do 20240101 as date. That's probably fine. Union all, and then what we're gonna do down here is we're gonna say, date of, date's a keyword. Let's just call it d.

We'll say date of d with a modifier of, what do you wanna do, plus 1 day, and then select date from dates where date, when you renamed it d, is less than or equal to 2020 20241231. So, we gotta come down here and call this dates. Now, we have a full on listing of all of the dates in a year less than, so we don't get that 1 hangover there. There you go. Now, what would you possibly use this for?

You can imagine when you are, trying to run some kind of report and you're grouping up on created date or purchase date or whatever, and then you, you give that report back and you look at it and you're like, shoot, there's a bunch of missing dates in here. Maybe nobody bought anything on the weekend or nobody signed up on a particular holiday and so that's going to be missing. But what you can do is you can start with dates and then left join in your other data and then you will have every single date in the year even if there was no data for that date. Even if that date's not present in your database, you still get it on your report which can be really nice and really, really useful. Generating data.

Very, very cool. Sometimes very, very useful, especially if you're trying to fill out gaps in a series. However, what if we wanna work with our own data? What if we have 1 of those tables that is self referential into the parents and children and we wanna come up with the tree that connects them all. We can definitely do that.

So here's a new table here. Select star from categories, and I'll have the link down below where you can get this data or spin it up on TURSO. So this is a very simple categories table. So if we start all the way down at where do you want to start? Let's start with Canon.

I have a Canon camera. So canon, the parent ID to canon is number 12. So canon lives in the cameras category. The cameras category lives in the video gear category. The video gear category lives in the electronics with.

So with. So let's put together a recursive CTE to map the path through all of these things. This is gonna be a wild ride, so hang on tight. Let's do with recursive all categories as, and our let's just go ahead and select it. Select star from all categories.

Our initial condition, our initial condition is select star from categories where parent ID equals null. Parent ID is null. This is our what do we call this? We call this our root node, let's say. This is the very top.

This is the beginning of the tree structure. And then we're going to union all. And then what do we want our cursive what do we want our recursive condition to be? Well, it's gonna get a little wild. We're gonna select star from all categories.

So at this point, like in the first run through, you gotta think about it as a recursive constantly running thing. The first run through, it's just going to be electronics. Electronics is going to be the entire table because that's the only 1 where parent ID is null. So if we had electronics, what would we do next? We could say electronics, inner join categories.

Let's drop this down. Inner join categories on all categories dot ID equals categories dot parent ID. So if we do that and then we run it, it doesn't have the same number of columns. That is correct because we joined. So we'll do ID and name I think is correct.

And then id, we want, categories dot id and categories dot name because all categories is the parent. So now if we run that we don't get any information but it kinda worked. So let's keep going here. Let's make some space down here. And then we'll make a little delineation there.

Alright. So we're getting close but we don't really see anything yet. Let's say that, we are gonna call this 1 as depth and then we'll make some more space here. And then here we're gonna say depth plus 1. And what does that get us?

Okay. That gets us somewhere. So we see that Canon camera is a depth of 4, meaning it's got 3 parents above it. So that's already kind of cool. It's not super cool, but it's kind of cool.

I wanna chart the path through this tree. I wanna chart this path. And so what we can do here is we can say, let's just say that this root node, so ID as path, and then down here we can say concat ID, Nope. Concat path from the row above, path plus a little fun arrow, plus dot ID down here. And now if we run that again, hey, now we've got a cool looking tree structure.

So Canon comes from 1 to 11 to 12 to 13. So 1 electronics to video gear which is 11, to cameras which is 12, to 13 which is Canon. And so now we've taken this, we've taken this, like, really rigid description or, display of the data and we have turned it into more of a linear structure. So we can see, hey, this is the hierarchy. This is the hierarchy of categories.

You can imagine this on tags or any deeply nested, association where it could go infinitely many deep. This is how you can come up with the path in just SQL. Could you do this on the application side? Sure. Is that a good idea?

I have no idea. Maybe. But at least now you know how to do it on in the in the SQL side and that might serve you well in the future. So recursive CTEs are incredibly powerful. They're super fun and they're a little bit mind bendy which is kinda fun.

You can combine recursive CTEs, regular CTEs. You can combine everything that we've learned about. And you, at this point, if you know all of this stuff, you are a SQL wizard to say nothing of being a SQLite wizard. This is just good SQL. This works in MySQL.

I'm pretty sure, but maybe with some syntax differences, this works in Postgres as well. And having the ability, having recursive CTEs in your tool belt is going to make you incredibly, incredibly powerful.