Common Table Expressions in Yukon
A Common Table Expression closely resembles a non-persistent view. A CTE is a temporary named result set that you define in your query that will be used by the FROM clause of the query. Each CTE is only defined once (but can be referred to as many time as it is in scope) and lives for as long as the query lives. CTEs can also be used to perform recursive operations. Here is the syntax to create a CTE:
With <name of your CTE>(<column names>)
As
(
<actual query>
)
Select * from <name of your CTE>
An example of a simple CTE is shown here:
With LondonCustomersCTE
(Select * From Customers Where City='London')
Select * From LondonCustomersCTE
Here is an example where we get a list of all the count of Customers’ orders in the orders table as a CTE and then a simple inner join with the Customer’s table to return the full name of the customer. (Sure this can be done without a CTE, but think about the ease of aggregating in the CTE only, it makes your code much easier to deal with.)
The code is here:
With CustomerOrderCountCTE(CustomerID, OrderCount)
select CustomerID, Count(*)
from orders
group by CustomerID
Select CU.CompanyName, City, COC.OrderCount
From Customers As CU Inner Join CustomerOrderCountCTE As COC
on CU.CustomerID = COC.CustomerID
Order By COC.OrderCount Desc
The true power of CTEs are when you use then recursively. A recursive CTE is constructed from a minimum of two queries, the first or Anchor Member (AM) is a non-recursive query and the send is the recursive query or the Recursive Member (RM). The AM and RM are separated by a UNION ALL statement. Here is the syntax:
With SimpleRecursive( field names)
<Select Statement for Anchor Member>
Union All
<Select Statement for the Recursive Memember>
Select * From SimpleRecursive
See my article on SQLJunkies.com.
