 Hello, everybody, and welcome to the first lesson in the advanced MySQL tutorial series. Today, we're gonna be looking at CTEs. Now, CTEs stand for Common Table Expression. They're gonna allow you to define a subquery block that you can then reference within the main query. Now, that may not make perfect sense, but we've looked at subqueries in the past or in previous lessons in the intermediate series. So you kind of understand that it's kind of like a query within a query, except we're gonna name this subquery block, and it'll be a little bit more standardized, a little bit better formatted than actually using a subquery. Let's take a look at the basics of writing a CTE. Let's pull this down really quickly. And all we're going to do is we want to create this as a CTE. We'll say with, and that is our keyword to define our CTE. So we're gonna say with, and then we're gonna name our CTE. And we'll just call it CTE, and we'll do underscore example. And then we're gonna say as. So this is how we define it. And now we need to actually put it in parentheses. Now you can do this in several different ways. I'm gonna do it kind of like this, just to really emphasize that this is within the CTE. Now CTEs are unique because you can only use the CTE immediately after you create it. So if you come right down here, and we come right below it, we'll say select everything. And we're gonna say from CTE example, we'll say from CTE example. And let's bring this back up. Now if we run this, we're gonna get the exact same output. Now this should seem pretty familiar, almost like we're using a subquery. And within our subquery, we have this right here, we're kind of building our own little table. And then we can query off of it down below. So we can come down here and let's actually change the names in here. We're gonna say average underscore cell. And we'll change all of these real quick, just because I don't like having to actually put the tick marks. I don't like doing that. So here we're gonna say max, then we'll say min, and then we'll say count. And let's go ahead and run this again. And so now we have these different names. And when we come right here, we can say select, and then we'll just do something really simple, let's do the average of average underscore cell. So the average salary. And let's run this. And so this is the average between both the males and the females kind of the purpose of these CTEs is to be able to perform more advanced calculations, something that you can't easily do or can't do at all, within just one query. Another reason to use a CTE is just the readability. You can absolutely write this using a subquery. And let's do that really quickly. And it's just going to be a little bit tougher to read and look at. So let's come right up here. We're gonna say, from, and we'll do right here, we'll say select everything we'll do select average cell. From here, and we're gonna need to name this, we'll say, do example underscore subquery. I'll get rid of this. And then we just need to get rid of this. And we can run this query, and we get the exact same output. Now, if I form out of this exactly the same, just like this, and the names down there. If we look at this, the syntax is just a little bit more difficult to read, we're selecting the average of average cell from and then we have our subquery right here. And then we're naming it at the bottom. If we scroll up and compare this, this one just looks a lot better. Now, when you're writing in MySQL, sometimes it doesn't matter if it looks pretty or not as long as it gets the job done. That is true, especially if you're just going to be using it yourself. But in a more professional environment, when you're using this in your actual job, they're going to be people who've been using this for 1020 years, and they're going to expect you to write it well. They don't want it to be really messy. They aren't most likely going to want it to be written like this. I've been using it for quite a long time. And I much prefer CTEs over subqueries just visually, and it makes a lot quicker to actually read through. So that is just one of the reasons, although you get the exact same output. Now, there is some additional functionality within CTEs as well. Now, one thing that I mentioned just a second ago, is that when you build a CTE, you can only use it immediately after you can't use it right below it. So let's go ahead and let's copy this query. And we're going to bring it right here. If we try to run this, and let's do this, we're going to get an error. And let's pull this up. It says table parks and rec dot CTE example doesn't exist. So we're looking for a table called CTE example in our database, but it's not there. Now, the reason this happens is because you're creating a CTE, you're not creating a permanent object, like a temp table, which we'll look at in the next lesson. And you're not creating a real table, and you're not creating a view, you're really not creating anything. It's just a common table expression to create this table right here. This basically almost like a temporary table almost. But then you're just using it to query off of it. You're not saving it, you're not storing it in memory. You're not really doing anything with it. It's just like writing a regular query. So this is why you can only write it immediately after creating the CTE, you can't write it down below and reuse it, because it's just like calling a query that you wrote before, it just isn't going to work. And the next thing I want to take a look at, and let's copy this down here. Next thing I want to take a look at is that you can actually create multiple CTEs within just one. So if you wanted to do a more complex query, or joining more complex queries together, we can do that all within one CTE. So let's come right here. Let's get rid of all of this. And we're going to say, from the demographics table, we're going to say where birth underscore date. And let's just do as larger than 1985 dash 01 dash 01. So we have one query. And we'll take a few columns from this table. So we'll take, let's say the employer or employee ID, we'll take the gender and the birth underscore date. So this is one query. And we're filtering just based off of this birth date. Now, when we create this, this is the CTE example, but we can have a comma here, and come down below. And then we can say CTE underscore example two. And I need to combine that. So two. And then we can say as, and then we have another query. So then right here, we could say select everything will change in a second from employee, say salary. And in the salary, we'll just do a simple one, we'll do where salary is greater than 50,000. And we'll actually just take the employee underscore ID, and the salary. Now, if I come right down here, I can say select everything from CTE example, which is, and let me scroll up, we can see everything. That's our original, our CTE example is this first query right here. Then we're creating our second one right here. And we can join basically on these two common table expressions. So now we can say join, and then we'll do CTE underscore example two. I need to change that X. And then we'll say on, then we're just going to do CTE example. employee underscore ID is equal to CTE example two dot employee underscore ID. And not an equal sign, but a dot, there we go. Now if we run this, it should work. And we can pull this down and look at our output. Now this is just an example. This isn't a real use case, because of course, we just joined these two tables together normally. But you can imagine you have a much more complex query, or you're doing a lot of functionality within this table, and you just want a certain subsection of this table. And you're wanting to combine those, this is how you can do that with a CTE. So now we have all of our information right here. And that can be extremely, extremely helpful. Now one last thing that I want to show you, we're gonna go all the way back up really quickly, right here. Let's run this one one more time. And let's actually take everything. And let's run this. So here we have our gender, average salary, max salary, min salary, and count salary. The last thing that I want to show you, and this is more of something that's just somewhat helpful, you don't have to actually do it in your main query is before we went in here, and we changed all the column names by doing an alias by saying as, and then saying the average salary, and the as is just implied here. But we're changing this via an alias. We don't have to do this. In fact, we could come right here, and we could do a parentheses, we could call it gender, we could call it average underscore salary, max underscore salary, min underscore salary, and let's do count underscore salary. So now if we were to run this, let's change this up, we'll do capital on this one. If we wanted to run it like this, when we run this, it'll change all of those names to what we have it right here. So this will be the default, this will overwrite the column names that you have in your actual CTE expression or the query that you have within your CTE. So that is all we're going to take a look at in this lesson on CTEs. These are very, very helpful, definitely help with more complex queries. And they're just really easy to read and understand, which is why I personally use them a lot. In the next lesson, we're going to be taking a look at temp tables, and we'll also compare temp tables to CTEs, and we'll take a look at the difference.