SQL CTE Explained

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:

1. Initiate a CTE using the WITH operator.
2. Specify the expression name for the soon-to-be-defined query.
3. Optional: Specify the column names separated by commas.
4. After assigning the expression name, follow it with the AS
5. Define the query to produce the desired result set.
6. If multiple CTEs are required, initiate each subsequent expression separated with a comma, and then repeat steps 2-4.
7. 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.

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

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

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.