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):
[sql];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;[/sql]
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:
[sql];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; [/sql]
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:
[sql] ;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; [/sql]
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.
[sql] ;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; [/sql]
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