In this blog, we’ll discuss some common table expressions, also known as CTEs. If you encounter complex problems, using these SQL table expressions can help you.
With CTE, you can break any task into small and manageable queries to solve complicated problems.
Now let’s explore how CTE can make your work more efficient in troubleshooting codes.
Syntax For Creating Common SQL Table Expressions
First, you have to type “WITH” followed by your CTE Expression name, followed by “AS”.
After that, you can provide any queries between the parentheses. This will be stored in the provided expression name that will work as a virtual table.
Then, add a SELECT statement using the virtual table you’ve created via the Expression name you’ve set.
For our example, we’ll use “USACusts” for the Expression name.
And for the query, we’ll use this code.
This query will create a virtual table. It’ll show a total or aggregated amount that’s grouped by customer ID where the country they belong to is “USA.” The data will be extracted from the Sales.Customers table.
Two columns will be visible, which are custid and Agg_Amount. Take note that the USACusts table is not a physical table but only a virtual table. That means it won’t be visible in our database.
After the query, we can then add a SELECT statement.
This statement will select all Agg_Amount that are greater than 1000 from the USACusts table.
Multiple Common SQL Table Expressions
Multiple CTEs can help you solve complex errors in your code by breaking them into smaller chunks. Additionally, you can define as many CTEs as you want. Below is an example of multiple CTEs.
For this example, we have the first CTE which is C1. From there, we selected YEAR(orderdate) and created 2 columns which are orderyear and custid from the Sales.Orders table.
For the second CTE named C2, we selected orderyear and gathered the number of custid using the COUNT command. Then we named it as numcusts from C1. A WHERE condition was also added in C2 to select only the orderyear that’s greater than 2015. Then we grouped them by orderyear.
After that, we selected the orderyear and numcusts from C2. Then we added a WHERE condition to select only the number of customers that’s greater than 500.
To sum up, this code is used to select customers where the orderyear is greater than 2015 and select only the number of customers that are more than 500 based on the orderyear condition.
Using Multiple CTEs For Solving Complex Codes
Next, I’ll demonstrate more examples to further emphasize the usage of CTEs in troubleshooting a big set of data. Here’s another example of multiple CTEs.
Please note that you can name CTEs the way you want but for this blog, we’re using C1, C2, C3, and so on to easily identify and understand how we utilize them in our examples.
Going back to the example, we created C1 to select only the sales where the order quantity is greater than 5 from the Sales.SalesOrderDetails table.
Then in C2, we utilized the data that we stored in C1 to join the product based on the Product.ID and grouped them by product name (p.Name) and product color (p.Color).
Since we declared a condition in C1, we’ll only group the product name and product color in C2 for orders where the order quantity (OrderQty) is greater than 5.
After that, we’ll use a command to select all data from C2 with an order quantity that’s greater than 1000.
For troubleshooting purposes, you can simply select each of the CTEs that you’ve created to check if it’s showing the correct data. Refer to the examples below.
For C1, it’s showing all the data from the Sales.SalesOrderDetail table with an order quantity that’s greater than 5.
Then C2 shows an output of the order quantity that’s grouped by product name and color from C1. In C2, we can see the orderqty column but we actually don’t have that in C2 because it’s just a calculated column that we generated from C1. From here, we can then start using any condition on the orderqty column and perform troubleshooting.
So, now you can see how multiple CTEs can help us in solving errors in complex codes by storing them in CTEs and start troubleshooting without damaging the actual code.
Using CTEs With Case Statement
Let’s have another example with the same context to show how we can utilize multiple CTEs in troubleshooting complex codes.
In this case, we’re going to use a CASE statement that will create a Product Category column with results that are based on the aggregated amount of orderqty from C2.
As mentioned earlier, we didn’t have orderqty in the first CTE as well as Product Category but we utilized intermediate tables to calculate and perform different calculations.
In the next example, we’ll create an aggregated amount based on the Product Category that we’ve created in the previous example. But to do this, we’ll put the CASE statement for the Product Category in the third CTE which is C3.
Before proceeding, let’s check the output of C3 by adding “SELECT * FROM C3” below C3.
In C3 we can see the ProductName, Color, orderqty, and Product Category columns. From here, we can start aggregating based on the Product Category. Let’s do this by using the following command below C3.
The example below is the result after creating an aggregated amount by using the command above.
Initially, we didn’t have the Product Category and orderqty columns but we’re able to generate sample outputs based on these columns by utilizing multiple CTEs.
At this point, you can see that we can do anything with the help of CTEs in working with our codes more efficiently.
***** Related Links *****
SQL Temp Tables And Views For Power BI User
Filter In SQL Using IN, NOT IN, LIKE, And NOT LIKE
SQL Data Extraction Using OFFSET And FETCH
Conclusion
In this tutorial, you’ve learned the proper syntax in creating Common SQL Table Expressions and how to utilize multiple CTEs in troubleshooting complex codes by isolating small parts of data and storing them inside a given CTE.
By learning this, you now have the ability to debug your codes more efficiently. Remember that you can use any name for CTE and you can’t use or call any CTE unless it is created. Once you get familiar with CTE, you can consider yourself an intermediate user of SQL.
If you’d like to know more about this topic and other related content, you can certainly check out the list of relevant links below.
All the best,
Hafiz
