Common Table Expression

When to Use
Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed (as when listing just the employees and their subordinate count in the example above), it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we've looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005's ranking functions - ROW_NUMBER(), RANK(), DENSE_RANK(), and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy. (For more on SQL Server 2005's ranking capabilities, be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.)
CTEs can also be used to recursively enumerate hierarchical data. We'll examine this next!

Common Table Expression Syntax
A Common Table Expression contains three core parts:


• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

A Simple Common Table Expression Example
Before we CTEs in detail, let's start by looking at a simple example.

WITH ProductCategoryNamesOverTenDollar
(ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

Once the CTE has been defined, it must then immediately be used in a query.
In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Using Multiple CTE’s
You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. For example, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match together the records from the two CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
SELECT
CategoryID,
CategoryName,
(SELECT COUNT(1) FROM Products p
WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
SELECT
ProductID,
CategoryID,
ProductName,
UnitPrice
FROM Products p
WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
INNER JOIN CategoryAndNumberOfProducts c ON
p.CategoryID = c.CategoryID
ORDER BY ProductName

Recursive Common Table Expressions
Recursion is the process of defining a solution to a problem in terms of itself. For example parent child relationship in employee table.

SELECT query that returns the rows from the CTE:
WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS
(
-- Base step
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo,
1 as HierarchyLevel
FROM Employees
WHERE ReportsTo IS NULL

UNION ALL

-- Recursive step
SELECT
e.EmployeeID,
e.LastName,
e.FirstName,
e.ReportsTo,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON
e.ReportsTo = eh.EmployeeID
)

SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName

This will return all the employee and their hierarchy level: