Secret life of CTEs

Common Table Expressions (CTEs for friends) is one of the most powerful, but still much underestimated, techniques in T-SQL.

So, what are CTEs? In shortest, they represent temporary result set, which can be then used later in the query. In practice, we can use them to avoid complex joins and subqueries and make our code more readable and easier to maintain.

Let’s take a look at simple example, which shows simple use of CTE (I will use Adventure Works 2012 database for this purpose):

;WITH employeeData as (
             SELECT C.FirstName
                   ,C.MiddleName
                   ,C.LastName
                   ,C.EmailAddress
                   ,E.MaritalStatus
                   ,E.BirthDate
             FROM Person.Contact as C
                 INNER JOIN HumanResources.Employee as E
                     on C.ContactID = E.ContactID
                 )

 SELECT FirstName
       ,MiddleName
       ,LastName
       ,EmailAddress
       ,MaritalStatus
       ,BirthDate
 FROM employeeData;

As you see, you can encapsulate complex logic within the CTE, which will make your code more readable. You can also combine multiple CTEs, by specifying them in the following way:

;WITH contactData as (
                         SELECT C.FirstName
                                ,C.MiddleName
                                ,C.LastName
                                ,C.EmailAddress
                                ,C.ContactID
                          FROM Person.Contact as C
                          )
      ,salesContactData as (
                          SELECT S.OrderDate
                                ,S.ContactID
                                ,S.SubTotal
                          FROM Sales.SalesOrderHeader as S
                        )

 SELECT C.FirstName
       ,C.MiddleName
       ,C.LastName
       ,C.EmailAddress
       ,S.OrderDate
       ,S.SubTotal
  FROM contactData as C
       INNER JOIN salesContactData as S
                  ON C.ContactID = S.ContactID;

This query returns contact data for persons which made purchases.

Deleting duplicate rows

One of the most powerful features of CTEs, is that you can perform not only SELECT statements, but also INSERT and DELETE. Let’s say that for any given reason, you have duplicates in your table that need to be deleted. Let’s find duplicates and eliminate them with one single command.

Imagine that you’ve entered by mistake two promotions with exactly same name and conditions and you want to get rid of one of them:

;WITH duplicates as (
                         SELECT Description
                                ,DiscountPct
                                ,Type
                                ,Category
                                ,StartDate
                                ,EndDate
                                ,ROW_NUMBER() OVER(PARTITION BY 
                                              Description,DiscountPct,Type,
                                              Category,StartDate,EndDate
                                              ORDER BY StartDate) as rnm
                          FROM Sales.SpecialOffer as SO
                          )
         DELETE
         FROM duplicates
         WHERE rnm > 1;

Recursive CTEs

Recursion is a well known paradigm from programming world. Simply said, it is the piece of code (procedure, function, etc.) which calls itself definite number of times in order to solve a problem.

It’s often being used to display multi-level hierarchies (employees – managers relationships, for example), but I will show here much simpler example, which shows weekdays starting from Monday.

;WITH daysOfWeek (num, day) 
 AS (
     SELECT --Anchor member of recursive CTE
         0 as num, 
         DATENAME(DW, 0) as day
     UNION ALL
     SELECT --Recursive member which use result from CTE itself    
         num + 1, 
         DATENAME(DW, num + 1)
     FROM daysOfWeek
     WHERE num < 6
 )
 SELECT day
 FROM daysOfWeek;

Conclusion

As I’ve already stated at the beginning, CTEs are very powerful technique for achieving various goals:

• Substitute database views and keep complex logic within query itself
• Improve readability by eliminating complex joins and subqueries
• Simplify usage of ranking functions, such as ROW_NUMBER, RANK, NTILE
• Offer flexibility for using recursive techniques in the query

This was just a short intro to Common Table Expressions. You can find more in-depth view here

Last Updated on January 2, 2020 by Nikola

Spread the music: