Common Table Expressions (CTE) in SQL

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:

WITH cte_name AS (
  SELECT column_1, column_2, column_3
  FROM table
)
SELECT column_1, column_3
FROM cte_name
WHERE ...

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.

CTEs are created at runtime and last only for the duration of the query itself.

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.

Recursive CTE Example

A recursive CTE is useful when the rows in a table naturally point to other rows in the same table. Common interview examples include employee-manager relationships, folder trees, category hierarchies, and family trees.

A recursive CTE usually has two parts:

• Anchor member: the starting row or rows.
• Recursive member: the query that repeatedly joins back to the CTE until no more rows are returned.

For example, imagine an Employees table with columns employee_id, name, and manager_id. To find every employee under Alice, we can start with Alice and then repeatedly find employees whose manager_id matches someone already in the CTE.

```sql
WITH RECURSIVE employee_tree AS (
  -- Anchor member: start with Alice
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS depth
  FROM Employees
  WHERE name = 'Alice'

  UNION ALL

  -- Recursive member: find direct reports
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    employee_tree.depth + 1 AS depth
  FROM Employees e
  JOIN employee_tree
    ON e.manager_id = employee_tree.employee_id
)
SELECT *
FROM employee_tree;
```

In a recursive CTE, what is the main purpose of the anchor member?

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):

Imagine we’d like to get the same table as above, but with one extra row – the Average age of all people that ordered the same number of items that year. In order to do this, we can use a Common Table Expression, as shown below:

WITH Avg_age AS (
  SELECT
    EXTRACT(year FROM DateOfOrder) AS year,
    NumberOfItems,
    AVG(Age) AS AverageAge
  FROM Table1
  GROUP BY EXTRACT(year FROM DateOfOrder) , NumberOfItems
)
SELECT
  Table1.*,
  avg.Avg_age
FROM Table1
JOIN Avg_age avg    
  ON EXTRACT(year FROM Table1.DateOfOrder) = avg.year
    AND Table1.NumberOfItems = avg.NumberOfItems

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

Imagine you have a table similar as the one above, but instead of having a column NumberOfItems, each order of each customer is recorded as a separate row. That is, there would be 3 rows with the name Steve, 2 for Alex, etc. Moreover, we have the following query:

WITH number_orders AS (
        SELECT Name,
                COUNT(*) AS order_count
        FROM table
        GROUP BY customerID)

SELECT AVG(order_count) AS avg_order_count
FROM number_orders

In case we run this query on the table, what would we get?

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.
• - A recursive CTE starts with an anchor query, then repeatedly runs the recursive query until no new matching rows are found.
• - The anchor member is the starting point of a recursive CTE.
• 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.