Hopefully, you're getting more and more comfortable with accessing your data, whether that's side by side, over under, filtering based on related, whatever it is. Now we're gonna talk about CTEs, which is a nice way to logically and structurally organize your queries, but can also have performance benefits as well. Before we do that, let's go back to our example from earlier where we are, joining up we're joining up or we're rather we're unioning these two queries. So if we run that, we get the 2 Aaron Francis's there. And, I did show you that you could do something like select star from this as temp and that will give you the same thing again.

This starts to get a little bit unwieldy. We kinda wanna, what we're doing is we're addressing this as a table. And when you have multiple when you have multiple conditions like this and you're building up this really complicated query as you go, it might make more sense to refactor that into a CTE. So let me copy this guy out and what you can do is you can say with table name as and then you open parenthesis and you can do, we're gonna put something in here but let's just do select 1. And then down here you say select star from table name.

And there's your one right there. So in our case, we're gonna name this, let's just call this users all. And then coming down here, notice we don't put a semicolon here. We're gonna say users all. So select star from users all and then in here we put our subquery or rather our 2 queries that we're unioning together to create this users all table and that gives us the 2 Aaron Francis's.

Likely, it's gonna look something more like this where you would take that where statement and come out here and say where email equals Aaron Francis. So here you're just reconstructing archived users and you're putting them together and then you're treating it as if it is a regular table which is really, really nice. You can keep going. So you can say with users, Erin as select star it's this. Select star from users all, where the email is my email, and then we can say from users aaron and we get we need a comma here.

We get the we don't need a with there. We get the same result. Forgive me on the syntax. So you have your CTEs all declared at the top and they can rely on each other. They can rely on each other and you can just keep going.

And this is a really good way to logically clean up some of your more complicated queries and to be able to reuse them. So you can imagine on the ORM side, so if you have an application that has an ORM, I love ORMs. Please use ORMs. They're awesome. You can imagine declaring this table and then just running your ORM across users all.

And so that's a good that's a good way to build up, what could be temporary or intermediate tables and then operate on them as if they're full on tables. Another great benefit of CTEs is that it can prevent SQLite from running a subquery multiple same subquery, it can make a lot of sense to pull that up to a CTE and have it run that CTE one time and then reference it as many times as you want. So it's not only for organization, although it is great for organization, it can also be a performance boost if you're referencing it multiple times and you pull it out to the top.