Basic syntax and usage of CTEs
The CTE syntax is as follows:
WITH cte_name (column_1, column_2, column_3,...)
AS (cte_query_definition)
SELECT *
FROM cte_name;
Such queries consist of two parts, and the first part is the CTE. Common Table Expressions are always introduced by the keyword WITH (that's why a CTE is also called an SQL WITH clause, and you can see this definition in many resources).
Thus, the parameters of the query with a CTE are:
- WITH – the introductory keyword necessary to define using the CTE. Only one WITH keyword is allowed.
- cte_name – the current Common Table Expression name.
- cte_query_definition – the actual CTE you apply.
The second part of the query syntax is a SELECT statement. You should write it right after the CTE without any columns, semicolons, or other punctuation symbols.
Now, let us delve into the practical usage of the CTEs.
Using the simple WITH clause
The simplest query format contains one CTE: the SQL Server first runs the query in the WITH clause, fetches the data, and stores it within a temporary relation. Then the main query uses that temporary relation and delivers the final result.
In the example below, we use the SQL WITH clause to retrieve the list of top products by units sold.
WITH TopSoldProducts AS
(
SELECT
p.ProductID,
p.Name AS ProductName,
SUM(sod.OrderQty) AS TotalQuantitySold
FROM
Production.Product p
JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
GROUP BY
p.ProductID, p.Name
)
SELECT
ProductID,
ProductName,
TotalQuantitySold
FROM
TopSoldProducts;
Querying multiple CTEs in a single query
Some work scenarios require multiple CTEs to bring the result. There is no need to write several separate queries because SQL allows combining multiple CTEs in a single query with one of the SET operators, such as UNION or UNION ALL.
Note the following rules for constructing a query with multiple CTEs:
- Only one WITH keyword is allowed.
- CTEs must be separated with commas.
- A comma is not needed before the main query.
- Only one main query is allowed.
Let us apply CTEs to retrieve the information about products along with their associated subcategories and categories:
WITH ProductCategoryCTE AS (
SELECT
ProductCategoryID,
Name AS CategoryName
FROM
Production.ProductCategory
),
ProductSubcategoryCTE AS (
SELECT
ProductSubcategoryID,
Name AS SubcategoryName,
ProductCategoryID
FROM
Production.ProductSubcategory
),
ProductDetailCTE AS (
SELECT
p.ProductID,
p.Name AS ProductName,
p.ProductNumber,
p.Color,
p.ListPrice,
ps.SubcategoryName,
pc.CategoryName
FROM
Production.Product AS p
LEFT JOIN
ProductSubcategoryCTE AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
LEFT JOIN
ProductCategoryCTE AS pc ON ps.ProductCategoryID = pc.ProductCategoryID
)
SELECT
pd.ProductID,
pd.ProductName,
pd.ProductNumber,
pd.Color,
pd.ListPrice,
pd.SubcategoryName,
pd.CategoryName
FROM
ProductDetailCTE AS pd
ORDER BY
pd.ProductID;
Advanced usage of CTEs
In the previous section, we examined the scenarios of referencing simple CTEs, single or multiple in one query. Let us now consider more challenging cases - recursive CTEs and nested CTEs.
Recursive CTE
A recursive CTE involves referencing itself - the result subset returned by the WITH clause is repeatedly referenced until getting the desired result.
The recursive CTE syntax is as follows:
WITH RECURSIVE cte_name AS (
cte_query_definition (the anchor member)
UNION ALL
cte_query_definition (the recursive member)
)
SELECT *
FROM cte_name;
As you see, the basic structure of the recursive CTE syntax is similar to the standard syntax of the non-recursive CTEs. However, there are significant differences that you need to notice when building queries with recursive CTEs:
- WITH RECURSIVE is used to introduce CTE instead of just WITH.
- The first query definition is called the anchor member.
- The second part of the CTE that will reference itself is called the recursive member.
- UNION or UNION ALL connects the anchor member with the recursive member.
The most common use case for applying recursive CTEs is querying hierarchical data, such as organizational structures, menus, routes, etc.
The following example showcases a recursive CTE, which provides a hierarchical view of the employees within the organization. This view illustrates the organizational structure, where each employee is associated with their manager's EmployeeID and their level in the hierarchy (denoted as EmployeeLevel). This kind of report is beneficial in various professional situations.
DECLARE @MyEmployees TABLE
(
ManagerID INT,
EmployeeID INT,
Title NVARCHAR(100),
FullName NVARCHAR(200)
);
INSERT INTO @MyEmployees (ManagerID, EmployeeID, Title, FullName)
VALUES
(NULL, 1, 'CEO', 'Cedric Thornfield'),
(1, 2, 'VP of Engineering', 'Beatrice Wetherby'),
(1, 3, 'VP of Product', 'Rupert Blakemore'),
-- Level 2: Team Lead
(2, 4, 'Team Lead', 'Rosalind Hawkesworth'),
(3, 5, 'Team Lead', 'Percival Greyson'),
-- Level 3: Lead
(4, 6, 'Lead', 'Victoria Radcliffe'),
(5, 7, 'Lead', 'Julian Crayford'),
-- Level 4: Regular employees
(6, 8, 'Developer', 'Elliot Stratton'),
(6, 9, 'Developer', 'Dorothy Winters'),
(7, 10, 'Developer', 'Leonard Wycliffe'),
(7, 11, 'Developer', 'Camilla Tyndall');
WITH EmployeeHierarchy(ManagerID, EmployeeID, Title, FullName, EmployeeLevel) AS
(
-- Base case: CEO (Level 0)
SELECT ManagerID, EmployeeID, Title, FullName, 0 AS EmployeeLevel
FROM @MyEmployees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive case: Get all employees reporting to the previous level
SELECT e.ManagerID, e.EmployeeID, e.Title, e.FullName, EmployeeLevel + 1
FROM @MyEmployees AS e
INNER JOIN EmployeeHierarchy AS eh
ON e.ManagerID = eh.EmployeeID
)
-- Final selection of all employees in hierarchy
SELECT ManagerID, EmployeeID, Title, FullName, EmployeeLevel
FROM EmployeeHierarchy
ORDER BY ManagerID, EmployeeLevel;
Another advanced CTE usage technique is working with nested CTEs.
Nested CTE
Like subqueries, CTEs can also be nested, or, in plain words, we can use a CTE definition inside another CTE definition.
The syntax of the nested CTE is as follows:
WITH cte_expression_1 AS (
WITH cte_expression_2 AS (
cte_query_definition -- Defines cte_expression_2
)
SELECT * FROM cte_expression_2
)
SELECT FROM cte_expression_1;
In this example, cte_expression_1 references the cte_expression_2. When the CTE is defined, we can use it in the subsequent expressions. The most common use case for nested CTEs is applying aggregate functions inside one another, such as when we need to find the average minimum or maximum, define the group comparisons, etc.
The below example demonstrates using the nested query to process order data to identify and rank customers based on their total spending, then filter and display those who have spent more than 350.
DECLARE @Orders TABLE (
OrderID INT,
CustomerName NVARCHAR(100),
OrderDate DATE,
Amount DECIMAL(10, 2)
);
INSERT INTO @Orders (OrderID, CustomerName, OrderDate, Amount)
VALUES
(1, 'Alexander Montgomery', '2025-03-01', 250.00),
(2, 'Beatrice Fitzgerald', '2025-03-02', 150.00),
(3, 'Charlotte Winchester', '2025-03-03', 200.00),
(4, 'Edward Kensington', '2025-03-01', 300.00),
(5, 'Beatrice Fitzgerald', '2025-03-05', 180.00),
(6, 'David Harrington', '2025-03-02', 500.00),
(7, 'Evelyn Pembroke', '2025-03-03', 120.00),
(8, 'Edward Kensington', '2025-03-04', 100.00),
(9, 'Charlotte Winchester', '2025-03-04', 350.00),
(10, 'David Harrington', '2025-03-05', 180.00);
-- Using nested CTEs to process the data
WITH CustomerOrders AS (
-- Outer CTE: Group orders by CustomerName and calculate the total amount spent by each customer
SELECT CustomerName, SUM(Amount) AS TotalAmount
FROM @Orders
GROUP BY CustomerName
),
RankedCustomers AS (
-- Nested CTE level 1: Rank customers based on the total amount spent
SELECT CustomerName, TotalAmount,
RANK() OVER (ORDER BY TotalAmount DESC) AS Rank
FROM CustomerOrders
),
FilteredCustomers AS (
-- Nested CTE level 2: Filter customers who spent more than 350
SELECT CustomerName, TotalAmount, Rank
FROM RankedCustomers
WHERE TotalAmount > 350
)
-- Select and display customer names, total amount spent, and rank from the filtered result
SELECT CustomerName, TotalAmount, Rank
FROM FilteredCustomers;
CTEs vs. other SQL features
Discussing SQL Common Table Expressions (CTEs), it's essential to highlight other SQL features that bear resemblance to CTEs in various aspects. Specifically, these include temporary tables and subqueries. Let's examine how these features differ and determine the most fitting application for each.
CTEs vs. Temporary table
Temporary tables are genuine tables that support indexes and constraints. They remain available during the session and can be accessed by multiple users. In contrast, CTEs are transient datasets that exist solely within a specific query and are only accessible during that query's execution.
Their objectives vary as well. CTEs primarily aid SQL developers in enhancing code clarity. On the other hand, temporary tables prove beneficial when handling large datasets or when a particular result set needs multiple references.
CTEs vs. Subqueries
CTEs and SQL subqueries share many similarities, but their differences dictate their optimal use cases.
As established, CTEs are positioned at the beginning of a query and must have designated names. In contrast, subqueries are integrated inline and don't necessitate naming.
Subqueries are one-time-use within a query, while CTEs can be referenced multiple times and support recursion. It's also worth noting that subqueries are compatible with the WHERE clause using IN and EXISTS, unlike CTEs.
When to use CTEs vs. Subqueries
CTEs are optimal for:
- Enhancing query readability. While CTEs don't typically influence query performance, they assist SQL developers in comprehending the code.
- Recursive queries. CTEs are handy for querying hierarchical data since subqueries lack recursive capabilities.
Subqueries are optimal for:
- Employing IN or EXISTS in the WHERE clause. CTEs are not suitable here.
- Modifying data in a table with information from another table. Subqueries efficiently extract the required data portion and integrate it into an UPDATE statement for another table.
In various scenarios, the choice between a subquery or a CTE is at the developer's discretion since both tools can serve multiple operational contexts.
| 🛠 Feature |
📋 Characteristics |
🔄 Reusability |
📈 Best use case |
| CTEs (Common Table Expressions) |
Transient datasets within a query, named and positioned at the start. |
Multi-use |
Enhancing query readability and recursive queries. |
| Temporary tables |
Genuine tables with indexes and constraints, accessible during a session. |
Multi-use |
Handling large datasets or when results need multiple references. |
| Subqueries |
Inline queries without naming, compatible with WHERE clause using IN/EXISTS. |
Single-use |
Using IN/EXISTS in a WHERE clause or modifying data in another table. |
