Written by:
2009/06/30 09:49 AM 

A common table expression is SQL  expression that returns a temporary result set from a simple or complex query, defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can work with both DDL and DML statements.

Normally, views are used to break down complex queries into digestible chunks or to provide a set of subquery results that can be grouped and filtered. Sometimes views can be overkill. They are permanent objects at in the database. Normally this type of scenario means we we only need to reference this complex query in a single stored procedure or UDF. Another option available to use is to use a derived table. Unfortunately, derived tables can make the readability of the query a little bit confusing. The derived table must also be repeated for each use in a statement.

A common table expression looks very similar to a temporary table or view.  The only difference between a CTE and a view is that views are created as database objects, whereas CTEs are not created as objects in the database. A CTE is available only for a single statement, and in general, every CTE can also be a part of a view.

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)

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

WITH ProductAndCategoryNamesOverTenDollars 
(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 Products

SELECT *

FROM ProductAndCategoryNamesOverTenDollars

ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC 

A typical Common Table Expression might look like this:

WITH ProductAndCategoryNamesOverTenDollars 
(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 

Notice that we use the CTE immediately after it has been declared.

Multiple CTEs can be defined after the WITH keyword by separating them with a comma. Each CTE can be referenced by the CTE that follows it, building off of each other. The data manipulation language (DML) statement that follows the CTE definitions can also refer to any of the CTEs defined within the WITH clause.

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 

Using CTE’s

There are many great uses of the CTE. We cannot list them all in this one post. Perhaps one day we will look at other examples. But one great example that I would like to share is of a particular problem I faced the other day. IT was a delete problem. Say, you have to delete some rows in your table, but they are particular rows. You only want to delete the oldest rows. A simple select order by will give you your rows, but you can’t delete them.

After many hours of checking which would be the best way, I eventually decided on using a CTE. With the CTE I can select the rows I want, order them in Desc, then use that select statement as the basis of my delete command.

Consider the following:

with oldblogs as

(

SELECT *,ROW_NUMBER() OVER (order by addeddate desc) as Rownum
from blog_comments

where entryID =1

)

deletefrom oldblogs where rownum < 10

You could also use a top 10% or top 100 or what ever. The point is that CTE’s make life a little easier. Your TSQL is readable, you can doo all sorts shenanigans inside the CTE expression and then work on that expression.

Another great use of CTE’s is finding and deleting duplicate rows. For what ever reason, you might have duplicate rows in your table. The following is an example of what you can do with a CTE to find and delete duplicate rows.

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

TrackbackPrint

Tags:

Categories:

Location: BlogsThe Brave Programmer