Syntax of CTEs - SQL Tutorial

- [Instructor] Common table expressions, referred to as CTEs, are another powerful tool to use in SQL. CTEs are actually really similar to subqueries in terms of their uses, and many times they can be used interchangeably. CTEs are a named temporary result set that can be referenced in a query, typically used when the output you are trying to achieve involves complex query operations, like multiple subqueries or temp tables intertwined. This single named result set can be referenced multiple times within the same query. While the subquery is a query within a query, CTEs are different in that they're accompanied by the WITH clause and placed at the beginning of a query statement. Let's look at an example from earlier. In this example, we are rewriting the query from video 01/02 using a CTE. Here, you can see how this has a bit of a different structure than what we saw in video 01/02, starting with the WITH clause. Let's head over to the database to write this query out and discuss more about its syntax. As a reminder, this exercise uses the Red 30 Tech online retail sales table where we have different information for products sold at the Red 30 Tech Conference, such as the order date, order type, product names, quantities, and prices. So, let's write out the query we had from the slides. Here, we're going to rewrite the query from video 01/02 that outputs orders whose order total is the same or higher than the average total price of all other orders. Since we're constructing a CTE this time, the first thing we'll want to do is start with the WITH clause. Then we give our CTE a name. In this case, I'll call it average total, followed by the column from our expression that we want to extract, which is average underscore total. And finally, we end this line with the as clause. The next portion is where we'll see our CTE come into play. Since we're only interested in orders where the order total is greater than the average price of all orders, we can specify that in our CTE. We'll write select average order total as average underscore total from the Red 30 Tech online retail sales table. Notice how here the CTE expression is enclosed in a set of parentheses, just like it was when we used the subquery. Before we move on to the rest of the query, I want to point out that you can check the output of your CTE by running the CTE expression as its own standalone query, just like we did in our uncorrelated subquery example. Here, we get the same amount back that our average total is $1,386. Next, is where we'll incorporate our CTE with the rest of the online retail sales table. In a new line, we want to select all fields from the online retail sales table, like we did before, and then we'll also add comma average total, directly referencing the CTE as if it's a table in the database. Once we've referenced the query, we're able to use it to filter the result set by writing where order total is greater than or equal to the average total column. And here we go. Notice our query results are exactly the same as what we achieved when we wrote this query as a scaler subquery with 852 rows. This syntax contains an example of a CTE called a non-recursive CTE. Non-recursive CTEs are CTEs that do not reference themselves within the CTE expression, like we do here. These are the most common types of CTEs you'll see in practice. In order to best understand what a non-recursive CTE is, it's helpful to view an example of its counterpart, the recursive CTE, which we'll cover in the next video.