Introduction

A common table expression (CTE) is a temporary named result table produced by a SELECT, INSERT, UPDATE, or DELETE statement that can be utilized in another statement. The CTE is a powerful SQL element that aids in query simplification. When a query is executed, the CTE acts as a virtual table (with records and columns) that is generated, used, and then deleted. This table frequently serves as a bridge to convert data from source tables to the format required by the query.

Syntax

A WITH clause that comes just before a SELECT, INSERT, UPDATE, DELETE or MERGE statement can be used to construct CTEs. A comma-separated list of CTEs can be included in the WITH clause. You can use the following syntax:

1WITH cte_name AS (
2  SELECT column_1, column_2, column_3
3  FROM table
4)
5SELECT column_1, column_3
6FROM cte_name
7WHERE ...

Where cte_name is the CTE’s name, while the CTE query is SELECT column_1, column_2, column_3 FROM table. The WITH keyword is used to begin the CTE. The name of your CTE is then specified, followed by the query's body in parentheses. The main query is the one after the closing parenthesis; SELECT column_1, column_3 FROM cte_name.

Why are CTEs Helpful?

CTEs, like database views and derived tables, improve the readability and simplicity of complicated queries, making it easier for users to create and manage them. By breaking down normally complicated searches into basic building parts that may be used—and reused, if necessary—in the rewriting of the query, the complexity is reduced. Instances of use cases include:

• Having to use a derived table more than once in the same query.
• Performing the same computation numerous times across different query components.
• Substituting views in the database.

Types of CTEs

There exist two distinct types of CTEs: recursive and non-recursive:

• Recursive: Repeated procedural loops, or recursion, are used by recursive CTEs. This recursive query calls itself for as long as the condition stated is not satisfied. At least two CTE query definitions, an anchor member, and a recursive member must be present in the recursive CTE definition. Given that the recursive CTE keeps running until the query returns the complete hierarchy, it is helpful when working with hierarchical data.
• Non-recursive: These CTEs are simple and don’t include any repeated processing of a given sub-routine. An example of a query generating and making use of such CTE can be seen in the next subsection down below.

CTE Example

Let us now better understand the concept of CTEs by taking a look at how to create one. First of all, we should have a table that we’ll be working with; in our example, that would be the following table (named Table1):

1WITH Avg_age AS (
2  SELECT
3    EXTRACT(year FROM DateOfOrder) AS year,
4    NumberOfItems,
5    AVG(Age) AS AverageAge
6  FROM Table1
7  GROUP BY EXTRACT(year FROM DateOfOrder) , NumberOfItems
8)
9SELECT
10  Table1.*,
11  avg.Avg_age
12FROM Table1
13JOIN Avg_age avg	
14  ON EXTRACT(year FROM Table1.DateOfOrder) = avg.year
15    AND Table1.NumberOfItems = avg.NumberOfItems

The resulting table, after the execution of this query, would look like this:

One Pager Cheat Sheet

• A Common Table Expression (CTE) acts as a virtual table generated by a WITH clause that can be used for query simplication and data conversion.
• Yes, CTEs are created at runtime and `last only for the duration of the query itself.
• CTEs improve the readability and simplicity of complicated queries by breaking them down into basic building parts that can be reused, solving use cases such as using derived tables multiple times, performing the same computation multiple times, and substituting views.
• There exist two distinct types of CTEs: recursive and non-recursive; the former is useful for working with hierarchical data whereas the latter are simple and do not include any repeated processing.
• Using a Common Table Expression (CTE) we can group and join data from Table1 to retrieve a table with a calculated average age for each item number of year.
• The CTE number_orders is used to calculate the average number of orders per customer with the result being the avg_order_count.