Back to articles list April 8, 2021 - 6 minutes read SQL CTE Explained Zahin Rahman Nuclear Engineer by day and Data Scientist by night, Zahin is passionate about driving business with data. He has 5+ years of industry experience in energy and power generation and has a master’s degree in Engineering with a focus in Data Science. He loves learning, whether from an industry veteran or a high school science enthusiast. Tags: sql learn sql CTE Common Table Expressions In this article, you will learn about SQL Common Table Expressions (CTEs). This powerful SQL tool will help you simplify complex queries. Additionally, I will introduce you to the two main types of CTEs and some of their use cases. CTE Primer The Common Table Expression (CTE) was introduced to standard SQL to simplify long, complex queries (especially joins and subqueries). It is a temporary data set returned by a query that contains data separate from the main query. This temporary data set can be referenced in a subsequent query. The data set is considered temporary because the result is not permanently stored anywhere. So, it only exists for the duration of the query. In other words, it is available temporarily in the execution scope of SELECT, INSERT, UPDATE, DELETE, or MERGE statements. CTEs are used in queries where a derived table is unsuitable. Therefore, they are considered a neat alternative to using Temporary Tables. Simply put, the key advantage of a CTE is that it helps organize and simplify long, complex hierarchical queries and improves readability by breaking them down into smaller blocks. CTE Syntax Here is a quick summary of the ground rules for executing SQL CTEs: Initiate a CTE using the WITH operator. Specify the expression name for the soon-to-be-defined query. Optional: Specify the column names separated by commas. After assigning the expression name, follow it with the AS Define the query to produce the desired result set. If multiple CTEs are required, initiate each subsequent expression separated with a comma, and then repeat steps 2-4. Reference the above-defined CTE(s) in a subsequent query such as SELECT, INSERT, UPDATE, DELETE, or MERGE. Here is a sample pseudo-code syntax of a CTE: --CTE WITH expression_name_1 (column_1,…,column_n) AS (CTE query definition 1), expression_name_2 (column_1,…,column_n) AS (CTE query definition X) --Query using CTE SELECT expression_A, expression_B, ... FROM expression_name_2 Below are two examples. For the interest of this article, we have kept them relatively simple to relay their main essence. The syntax of SQL CTEs is explained in much more detail in Module #2 of the Recursive Queries course. This module also includes more advanced examples. Example 1 Let’s explore a simple example of a CTE in action using the following Orders table, which has recorded 196 individual orders. OrderIDCustomerIDEmployeeIDOrderDateShipperID 102489051996-07-043 102498161996-07-051 102503441996-07-082 102518431996-07-081 102527641996-07-092 OBJECTIVE: To return the average number of orders, or sales made, by each EmployeeID. QUERY: --CTE WITH cte_sales AS (SELECT EmployeeID, COUNT(*) OrderID FROM Orders GROUP BY EmployeeID) --Query using CTE SELECT AVG(OrderID) average_orders_per_employee FROM cte_sales; RESULT: Number of Records: 1 average_orders_per_employee 21.77777777777778 In this example: First, we used cte_sales as the name of the CTE. Second, we construct the query to define the result set. The query returned the count of the number of orders (OrderID) grouped by EmployeeID. Finally, we referred to cte_sales in the subsequent query and used AVG() to get the average sales per EmployeeID. Example 2 Here, we will demonstrate an example of Nested CTEs using the same Orders table as above. Nested CTEs involve two CTEs in the same query with the second one referencing the first. This is a good example of CTEs helping to break down long codes into smaller chunks for enhanced readability and debugging. OBJECTIVE: To return the average number of orders, or sales made, by each EmployeeID for a specific ShipperID (in this case, ShipperID = 3). QUERY: --1st CTE WITH cte_sales AS (SELECT EmployeeID, COUNT(OrderID) as Orders, ShipperID FROM Orders GROUP BY EmployeeID, ShipperID), --2nd CTE (nested) shipper_cte AS (SELECT * FROM cte_sales WHERE ShipperID=3) --Query using CTE SELECT AVG(Orders) average_order_per_employee_shipper2, ShipperID FROM shipper_cte; RESULT: Number of Records: 1 average_order_per_employee_shipper2ShipperID 7.5555555555555553 In this example: The premise of this example is quite similar to Example 1. The only addition is calculating the average number of orders per employee for a specific ShipperID (in this case, ShipperID=3). In the second CTE (shipper_cte), we referred to the first CTE (cte_sales) and defined the ShipperID=3 condition using a WHERE clause. One of our previous articles, CTE with examples, runs through some more specific examples. This will further help you wrap your head around the unique application areas of CTEs. CTE Use Cases (Why Do You Need CTEs?) Also referred to as WITH queries, CTEs were first introduced to SQL standard in 1999. They are now available in all major RDBMS. Some common use cases include: Referencing a CTE (derived table) multiple times in a single query Alternative to creating a view in the database Performing an identical calculation multiple times across several queries If you think CTEs sound similar to subqueries, you are correct! The only difference is that with CTEs, you can re-use the same data set multiple times in your code. The key advantages of CTEs can be summed up as the following: Improved Readability– Instead of lumping all of your query logic into one large chunk, you can create several CTEs. They can then be combined later in the query in a final SELECT (or another) statement. Substitute for a View– You can substitute a CTE for a view, as I mentioned above. This is especially useful if you don’t have rights/permissions to create a view object or don’t want to create one to only be used in a single query. Ranking– CTEs can be used with some of the ranking functions such as ROW_NUMBER(), RANK(), and NTILE(). You can find more information about this functionality in our article How to Rank Rows in SQL. Overcome Statement Limitations– CTEs help overcome constraints such as SELECT statement limitations (e.g., performing GROUP BY using non-deterministic functions). Recursion– This is one of the more advanced advantages and applications of CTEs. Recursive queries can call themselves and allow you to traverse hierarchical models of great complexity, such as organizational charts. A few more use cases of CTEs are discussed in our article about when to use CTEs. In addition, the Recursive Queries course on LearnSQL.com will help break down the topic of CTEs. With detailed walkthroughs and hands-on exercises, you will be on your way to mastering CTEs. Recursive CTEs The examples shown above were non-recursive CTEs. One of the more advanced functionalities of CTEs is Recursive CTEs. A recursive CTE is unique because it is allowed to reference itself within that CTE. This will allow you to traverse complex hierarchical data structures (e.g., organizational charts or an equipment bill of materials). The course Recursive Queries covers recursive CTEs in great detail with an immersive and engaging learning environment. The course also covers some of the more foundational aspects of CTEs before moving on to recursive CTEs. Conclusion In this article, you have learned how to use SQL CTEs to construct complex queries in an easy-to-understand manner. As you saw, SQL CTEs, whether recursive or non-recursive, can be a handy tool. CTEs allow you to generate temporary result sets that can be accessed later in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. In some ways, a CTE is like a derived table: it’s not stored as an object, and it is valid only during the execution of the primary statement. However, unlike a derived table, a CTE can be referenced multiple times within a query, and it can be self-referencing (recursive). Best of all, CTEs are relatively easy to implement! To gain a comprehensive understanding of recursive and non-recursive SQL CTEs, make sure to check out the Recursive Queries course. Tags: sql learn sql CTE Common Table Expressions You may also like SQL Course of the Month – Recursive Queries Recursive Queries is our most challenging SQL course. This article explains Common Table Expressions (CTEs) and how they help you write recursive SQL queries. Read more Where Can I Find Good SQL CTE Exercises? SQL Common Table Expressions (CTEs) are widely used to improve the performance and readability of SQL code. Learn about where you can find good SQL CTE exercises and resources. Read more What Is a CTE? We explain what CTE is and how to use common table expressions in SQL scripts. Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more Do it in SQL: Recursive SQL Tree Traversal Ever heard of the SQL tree structure? In this article, you’ll learn to use the recursive SQL tree traversal on the example of a website menu. Read more Get to Know the Power of SQL Recursive Queries Recursive Queries: skewness and kurtosis. Most commonly, the SQL queries we run on a database are quite simple. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.