The CTE was introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable. For some reason, it can be difficult to grasp the techniques of using it. Well, that's before Rob Sheldon explained it all so clearly for us.
Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.
SQL Server supports two types of CTEs—recursive and nonrecursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008 and retrieve data from the AdventureWorks2008 sample database.
Working with Common Table Expressions
You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:
[WITH <common_table_expression> [,...]]
<common_table_expression>::=
cte_name [(column_name [,...])]
AS (cte_query)
...which can be represented like this...
As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas. In addition, for each CTE, you must provide a name, the AS keyword, and a SELECT statement. You can also provide column names (separated by commas), as long as the number of names match the number of columns returned by the result set.
The SELECT statement in your CTE query must follow the same requirements as those used for creating a view. For details about those requirements, see the topic “CREATE VIEW (Transact-SQL)” in SQL Server Books Online. For more details about CTEs in general, see the topic “WITH common_table_expression (Transact-SQL).”
After you define your WITH clause with the necessary CTEs, you can then reference those CTEs as you would any other table. However, you can reference a CTE only within the execution scope of the statement that immediately follows the WITH clause. After you’ve run your statement, the CTE result set is not available to other statements.
Creating a Nonrecursive Common Table Expression
A nonrecursive CTE is one that does not reference itself within the CTE. Nonrecursive CTEs tend to be simpler than recursive CTEs, which is why I’m starting with this type. In the following example, I create a CTE named cteTotalSales:
WITH
cteTotalSales (SalesPersonID, NetSales)
AS
(
SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT
sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
ts.NetSales
FROM Sales.vSalesPerson AS sp
INNER JOIN cteTotalSales AS ts
ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC
After I specify the CTE name, I provide two column names, SalesPersonID and NetSales, which are enclosed in parentheses and separated by a comma. That means the result set returned by the CTE query must return two columns.
Next, I provide the AS keyword, then a set of parentheses that enclose the CTE query. In this case, the SELECT statement returns the total sales for each sales person (total sales grouped by salesperson ID). As you can see, the CTE query can include Transact-SQL functions, GROUP BY clauses, or any elements that the SELECT statement in a view definition can include.
I can now reference cteTotalSales in the statement that immediately follows. For this example, I create a SELECT statement that joins the Sales.vSalesPerson view to cteTotalSales, based on the salesperson ID. I then pull the names and locations from the view and the net sales from the CTE. The following table shows the results returned by this statement.
As you saw earlier in the syntax, you can include multiple CTEs in a WITH clause. The following WITH clause includes two CTEs, one named cteTotalSales and one named cteTargetDiff:
WITH
cteTotalSales (SalesPersonID, NetSales)
AS
(
SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
AND OrderDate BETWEEN '2003-01-01 00:00:00.000'
AND '2003-12-31 23:59:59.000'
GROUP BY SalesPersonID
),
cteTargetDiff (SalesPersonID, SalesQuota, QuotaDiff)
AS
(
SELECT ts.SalesPersonID,
CASE
WHEN sp.SalesQuota IS NULL THEN 0
ELSE sp.SalesQuota
END,
CASE
WHEN sp.SalesQuota IS NULL THEN ts.NetSales
ELSE ts.NetSales - sp.SalesQuota
END
FROM cteTotalSales AS ts
INNER JOIN Sales.SalesPerson AS sp
ON ts.SalesPersonID = sp.BusinessEntityID
)
SELECT
sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City,
ts.NetSales,
td.SalesQuota,
td.QuotaDiff
FROM Sales.vSalesPerson AS sp
INNER JOIN cteTotalSales AS ts
ON sp.BusinessEntityID = ts.SalesPersonID
INNER JOIN cteTargetDiff AS td
ON sp.BusinessEntityID = td.SalesPersonID
ORDER BY ts.NetSales DESC
The first CTE—cteTotalSales—is similar to the one in the preceding example, except that the WHERE clause has been further qualified to include sales only from 2003. After I define cteTotalSales, I add a comma, and then define cteTargetDiff, which calculates the difference between the sales total and the sales quota.
The new CTE definition specifies three columns for the result set: SalesPersonID, SalesQuota, and QuotaDiff. As you would expect, the CTE query returns three columns. The first is the salesperson ID. The second is the sales quota. However, because a sales quota is not defined for some salespeople I use a CASE statement. If the value is null, that value is set to 0, otherwise the actual SalesQuota value is used.
The final column returned is the difference between the net sales and sales quota. Again, I use a CASE statement. If the SalesQuota value is null, then the NetSales value is used, otherwise the sales quota is subtracted from the net sales to arrive at the difference.
Something interesting to note about the second CTE query is that I’ve joined the Sales.SalesPerson table to the first CTE—cteTotalSales—so I could calculate the difference between total sales and the sales quota. Whenever you define multiple CTEs in a single WITH clause, you can reference preceding CTEs (but not the other way around).
Once I’ve defined my CTEs, I can reference them in the first statement that follows the CTE, as you saw in the previous example. In this case, I join the Sales.vSalesPerson view to cteTotalSales and then join to cteTargetDiff, all based on the salesperson ID. My SELECT list then includes columns from all three sources. The statement returns the results shown in the following table.
As you can see, sales data is provided for all salespeople, including the city in which they reside, their net sales, their sales quota, and the calculated difference between the two figures. In this case, everyone well exceeds the quota, where a quota has been defined.
Creating a Recursive Common Table Expression
A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.
A typical example of hierarchical data is a table that includes a list of employees. For each employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data, as it would appear within the organizational chart.
Note that a CTE created incorrectly could enter an infinite loop. To prevent this, you can include the MAXRECURSION hint in the OPTION clause of the primary SELECT, INSERT, UPDATE, DELETE, or MERGE statement. For information about using query hints, see the topic “Query Hints (Transact-SQL)” in SQL Server Books Online.
To demonstrate how the recursive CTE works, I used the following Transact-SQL statements to create and populate the Employees table in the AdventureWorks2008 database:
IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees
GO
CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
ManagerID int NULL
)
GO
INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employees VALUES (102, 'Terri', 'Duffy', 101)
INSERT INTO Employees VALUES (103, 'Roberto', 'Tamburello', 101)
INSERT INTO Employees VALUES (104, 'Rob', 'Walters', 102)
INSERT INTO Employees VALUES (105, 'Gail', 'Erickson', 102)
INSERT INTO Employees VALUES (106, 'Jossef', 'Goldberg', 103)
INSERT INTO Employees VALUES (107, 'Dylan', 'Miller', 103)
INSERT INTO Employees VALUES (108, 'Diane', 'Margheim', 105)
INSERT INTO Employees VALUES (109, 'Gigi', 'Matthew', 105)
INSERT INTO Employees VALUES (110, 'Michael', 'Raheem', 106)
As you might realize, the AdventureWorks2008 database already includes the HumanResources.Employee table. However, that table now uses the hierarchyid data type to store hierarchical data, which would introduce unnecessary complexity when trying to demonstrate a recursive CTE. For that reason, I created my own table. However, if you want to try out a recursive CTE without creating and populating a new table, you can use the AdventureWorks sample database that shipped with SQL Server 2005. The HumanResources.Employee table in that database stores the data in a way similar to the table I create above.
After I created the Employees table, I created the following SELECT statement, which is preceded by a WITH clause that includes a CTE named cteReports:
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
As you can see, the CTE returns five columns: EmpID, FirstName, LastName, MgrID, and EmpLevel. The EmpLevel column refers to the level in the hierarchy in which the employees fit. The highest level of the hierarchy is 1, the next level is 2, followed by 3, and so on.
The CTE query is itself made up of two SELECT statements, connected with the UNION ALL operator. A recursive CTE query must contain at least two members (statements), connected by the UNION ALL, UNION, INTERSECT, or EXCEPT operator. In this example, the first SELECT statement is the anchor member, and the second statement is the recursive member. All anchor members must precede the recursive members, and only the recursive members can reference the CTE itself. In addition, all members must return the same number of columns with corresponding data types.
Now lets look closer at the statements themselves. The first statement, the anchor member, retrieves the employee ID, first name, last name, and manager ID from the Employees table, where the manager ID is null. This would be the employee at the top of the hierarchy, which means this person reports to no one. Consequently, the manager ID value is null. To reflect that this person is at the top of the hierarchy, I assign a value of 1 to the EmpLevel column.
The second statement in the CTE query—the recursive member—also retrieves the employee ID, first name, last name, and manager ID for employees in the Employees table. However, notice that I join the Employees table to the CTE itself. In addition, the join is based on the manager ID in the Employees table and the employee ID in the CTE. By doing this, the CTE will loop through the Employees table until it returns the entire hierarchy.
One other item to notice about the second statement is that, for the EmpLevel column, I add the value 1 to the EmpLevel value as it appears in the CTE. That way, each time the statement loops through the hierarchy, the next correct level is applied to the employees at the level.
After I define my WITH clause, I create a SELECT statement that retrieves the data from the CTE. Note, however, that for the Manager column, I retrieve the first and last name of the employee associated with the manager ID in the CTE. This allows me to display the full name of the manager for each employee. The following table shows the result set returned by the SELECT statement and its CTE.
As you can see, the CTE, whether recursive or nonrecursive, can be a useful tool when you need to generate temporary result sets that can be accessed in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. In a sense, a CTE is like a derived table: it’s not stored as an object and is valid only during the execution of the primary statement. However, unlike the derived table, a CTE can be referenced multiple times within a query and it can be self-referencing. And best of all, CTEs are relatively easy to implement.
You'll have noticed that Bob is using AdventureWorks2008 rather than AdventureWorks. If you prefer to run these examples against the AdventureWorks database rather than the AdventureWorks2008 database, you should change the BusinessEntityID column to the SalesPersonID column.