SQL CTE

A CTE, also known as a Common Table Expression in SQL, is a temporary result set that is defined within the scope of a single SQL query. It's a unique kind of subquery that can be used like a view in the query that defines it and can reference itself, which allows for powerful recursion.

Consider a SQL CTE as a named temporary result set that exists only for the lifespan of a query. It's mainly used for recursive queries, breaking down complex queries into simpler forms, and creating a recursive view.

CTE in SQL provides an easier alternative to derived tables and views. It doesn't store the result set physically and vanishes after execution, making it efficient for memory usage.

CTE Syntax

Before we see examples, let's take a look at the CTE syntax. The basic structure of a CTE in SQL looks like this:

WITH cte_name (column_name1, column_name2, ...)
AS
(
-- CTE query
)
-- Main query

1. The ”WITH” keyword is crucial, as it precedes the CTE name.
2. The CTE name is then followed by a list of column names enclosed in parentheses (optional, if not provided names will be as in the select statement of CTE).
3. And finally the “AS” keyword introduces the query that defines the SQL CTE. Once the CTE is defined, it can be referenced like a regular Table or View in a subsequent SQL statement.

SQL CTE Example

To get a better understanding of CTEs in SQL, let's create a hypothetical example. Suppose we have a main table "Orders":

Now, let's create a very simple CTE in SQL to select all orders made by customer "100".

WITH CustomerOrders AS
(
     SELECT * FROM Orders WHERE CustomerId = 100
)
SELECT * FROM CustomerOrders;

The output of SQL CTE will be:

In this simple SQL CTE example, we've used a CTE to fetch all orders for a specific customer. You can see the above SQL with CTE is very easier to read and can be used like a table.

A SQL CTE becomes especially useful when working with SQL JOIN operations and SQL Aggregate Functions like "SUM", "COUNT", "AVG" etc.

Consider the "Orders" table above and an additional "Products" table as follows:

Let's create a Common table expression SQL to find the total cost of orders for each customer:

WITH OrdersCTE AS (
    SELECT CustomerId, ProductId
    FROM Orders
),
ProductsCTE AS (
    SELECT ProductId, Price
    FROM Products
)
SELECT o.CustomerId, SUM(p.Price) as TotalCost
FROM OrdersCTE o
INNER JOIN ProductsCTE p ON o.ProductId = p.ProductId
GROUP BY o.CustomerId;

In this SQL CTE, we have used a JOIN operation between the "OrdersCTE" and "ProductsCTE", and "SUM" aggregate function to calculate the total cost of orders per customer.

The output of the above SQL CTE will be:

Note that to get the same results as the above SQL CTE query, we can also write a simple select query by joining the Orders and Products tables directly. The above query was just to show you the capability of SQL CTE so you can use that in advanced scenarios where simple Select queries are not enough.

See the following simplified version of the above SQL CTE:

WITH TotalOrderCost AS
(
    SELECT o.CustomerId, SUM(p.Price) as TotalCost
    FROM Orders o
    INNER JOIN Products p ON o.ProductId = p.ProductId
    GROUP BY o.CustomerId
)
SELECT * FROM TotalOrderCost;

CTEs in SQL are also handy when working with window functions like "ROW_NUMBER", "RANK", "DENSE_RANK" etc, often combined with "PARTITION BY" to achieve tasks like fetching the highest value per group.

Now, suppose we want to find the most expensive product bought by each customer. This requires using "ROW_NUMBER" and "PARTITION BY" inside a SQL CTE:

WITH ExpensiveProduct AS
(
    SELECT o.CustomerId, p.ProductName, p.Price, 
    ROW_NUMBER() OVER 
                     (
                       PARTITION BY o.CustomerId 
                       ORDER BY p.Price DESC
                     ) as RowNum
    FROM Orders o
    INNER JOIN Products p ON o.ProductId = p.ProductId
)
SELECT CustomerId, ProductName, Price 
FROM ExpensiveProduct 
WHERE RowNum = 1;

The output of this SQL CTE will be:

The "ROW_NUMBER() OVER (PARTITION BY o.CustomerId ORDER BY p.Price DESC)" portion of the query assigns a unique row number within each partition (here, each customer) in descending order of product price. Hence, "RowNum = 1" fetches the most expensive product for each customer.

SQL Server also allows us to define multiple CTEs within a single query. This is handy when we need to perform complex data manipulations that require multiple temporary result sets. We already saw this feature in SQL CTE with SQL JOIN section. Let’s have another example for practicing SQL CTEs, as practice makes it permanent! (if not perfect).

Returning to our "Orders" and "Products" tables, suppose we want to find the total cost and the count of orders for each customer. We can define two Common Table Expressions to perform these tasks separately:

WITH TotalOrderCost AS
(
    SELECT o.CustomerId, SUM(p.Price) as TotalCost
    FROM Orders o
    INNER JOIN Products p ON o.ProductId = p.ProductId
    GROUP BY o.CustomerId
),
OrderCount AS
(
    SELECT CustomerId, COUNT(*) as Count
    FROM Orders
    GROUP BY CustomerId
)
SELECT t.CustomerId, t.TotalCost, c.Count
FROM TotalOrderCost t
INNER JOIN OrderCount c ON t.CustomerId = c.CustomerId;

And the output of this SQL CTE is:

In this SQL CTE example, we have used multiple CTEs to perform two separate aggregations, and then joined the two result sets to provide a comprehensive view of each customer's transactions.

Nested CTE refers to a CTE within another CTE. They are useful when a multi-step transformation is needed, with each step building upon the previous one.

Let's say we want to calculate the running total of order costs for each customer. Here's how we can do this using nested CTE in SQL Server:

WITH OrderCost AS
(
    SELECT o.CustomerId, p.Price
    FROM Orders o
    INNER JOIN Products p ON o.ProductId = p.ProductId
),
RunningTotal AS
(
    SELECT CustomerId,
           SUM(Price) OVER (PARTITION BY CustomerId ORDER BY CustomerId) AS Total,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CustomerId) AS RowNumber

    FROM OrderCost
)
SELECT CustomerId, Total
FROM RunningTotal
WHERE RowNumber = 1;

Above SQL CTE output:

In this SQL CTE example, the first CTE ("OrderCost") computes the cost of each order, and the second Nested CTE ("RunningTotal") calculates the running total for each customer.

Definitely, this query can be simplified by using other techniques, but here our purpose is to show you the capabilities of SQL CTE to prepare you for advanced use.

A recursive CTE is a CTE that references itself. It has two parts: the anchor member (the base result set) and the recursive member (the iterative part that references the CTE itself). Recursive CTEs are powerful tools that can perform complex hierarchical or recursive tasks that would otherwise require procedural programming.

For example, let's calculate the factorial of a number using a recursive CTE in SQL:

WITH Factorial AS
(
    -- Anchor member
    SELECT 1 as Num, 1 as Factorial
    UNION ALL
    -- Recursive member
    SELECT Num + 1, (Num + 1) * Factorial
    FROM Factorial
    WHERE Num < 5
)
SELECT * FROM Factorial;

The output of the above Recursive CTE will be:

This SQL CTE example shows the power of SQL CTE with Recursive CTE. It starts with the base case ("Num = 1" and "Factorial = 1"), then in each recursion, it increments "Num" by "1" and calculates the factorial by multiplying the current "Num" with the previous factorial until "Num" reaches "5".