Post
Ask Question

Common Table Expression (CTE) In SQL Server

According to the CTE documentation, a Common Table Expression is a temporary result set or a temporary table, in which we can do CREATE, UPDATE, DELETE but within that scope. If we create the CTE in a Stored Procedure, I can’t use it in another Stored Procedure. So what we can say is that CTE is a derived table; it’s a temporary result set.

If you run these statements:

  1. SELECT * FROM Invoice  
  2. SELECT * FROM Customer  

You’ll get all the table attributes along with data inside. Now, let’s create a join and we just want some kind of specific attributes.

  1. SELECT Country  
  2.            , FirstName + ' ' + LastName AS CustomerName  
  3.            , SUM (Total) AS TotalInvoices  
  4. FROM Invoice  
  5. JOIN Customer  
  6. ON Invoice.CustomerId = Customer.CustomerId  
  7. GROUP BY Country, FirstName, LastName  

And this is our temporary result set containing Country, CustomerName, and TotalInvoices.

Now, if you go back and see the syntax of CTE.

  1. WITH CTENAME (x, y, z, any fields you want)  

So, with CTE, we would like to create a simple temporary table which has only 3 fields - Country, CustomerName, and TotalInvoices. And, we’ll use that temporary table for a short duration while the SELECT query is getting executed and then we don’t want it.

  1. WITH TotalInvoicesByCustomerAndByCountry (Country, CustomerName, TotalInvoices)  
  2. AS  
  3. (  
  4.     SELECT Country  
  5.            , FirstName + ' ' + LastName AS CustomerName  
  6.            , SUM (Total) AS TotalInvoices  
  7.     FROM Invoice  
  8.     JOIN Customer  
  9.     ON Invoice.CustomerId = Customer.CustomerId  
  10.     GROUP BY Country, FirstName, LastName  
  11. )  
  12. SELECT * FROM TotalInvoicesByCustomerAndByCountry  

Look, we have defined the CTE and then we have used it in the SELECT statement.

Multiple CTEs

We can also create multiple CTEs and make them comma separated. After specifying CTE Name, we have provided the above 3 column names as well (Country, CustomerName, TotalInvoices) which means that the result set returned by CTE must return these column names. But if we don’t specify them, then it will also work.

  1. WITH TotalInvoicesByCustomerAndByCountry  
  2. AS  
  3. (  
  4.     SELECT Country  
  5.            , FirstName + ' ' + LastName AS CustomerName  
  6.            , SUM (Total) AS TotalInvoices  
  7.     FROM Invoice  
  8.     JOIN Customer  
  9.     ON Invoice.CustomerId = Customer.CustomerId  
  10.     GROUP BY Country, FirstName, LastName  
  11. ),  
  12. TopCustomerByCountry  
  13. AS  
  14. (  
  15.     SELECT Country  
  16.             , MAX (TotalInvoices) AS MaxTotalInvoices  
  17.     FROM TotalInvoicesByCustomerAndByCountry  
  18.     GROUP BY Country  
  19. )  
  20. SELECT TotalInvoicesByCustomerAndByCountry.Country  
  21.         , TotalInvoicesByCustomerAndByCountry.CustomerName  
  22.         , TopCustomerByCountry.MaxTotalInvoices  
  23. FROM TotalInvoicesByCustomerAndByCountry  
  24. JOIN TopCustomerByCountry  
  25. ON TotalInvoicesByCustomerAndByCountry.Country = TopCustomerByCountry.Country  
  26. AND TotalInvoicesByCustomerAndByCountry.TotalInvoices = TopCustomerByCountry.MaxTotalInvoices  
  27. ORDER BY TotalInvoicesByCustomerAndByCountry.Country  

Look here -- we separate multiple CTEs by comma and remove the arguments from CTEs. We use 1st CTE in the 2nd and then we use both CTEs in the next query to join the data that we get.

On this temporary table, yes we can do CREATE, UPDATE, DELETE, SELECT. CTE can be used to create a recursive query which means we can create the CTE and call it recursively. It’s the substitute for the Views. Views are nothing but a temporary table. CTE also offers the advantages of improved readability and ease in maintenance of complex queries.

Types of CTE

There are two types of CTEs.

  • Non-Recursive
  • Recursive

Non-Recursive CTE

In Non-Recursive CTE, it doesn’t reference itself. It is much simpler than recursive. 

Recursive CTE

In the recursive CTE, it calls itself.

  1. WITH EmployeeCTE AS  
  2. (  
  3.     SELECT EmployeeId  
  4.             , FirstName + ' ' + LastName AS EmployeeName  
  5.             , 1 AS Tier  
  6.     FROM Employee  
  7.     WHERE ReportsTo IS NULL  
  8. UNION ALL  
  9.     SELECT Employee.EmployeeId  
  10.             , FirstName + ' ' + LastName  
  11.             , Tier + 1  
  12.     FROM Employee  
  13.     JOIN EmployeeCTE  
  14.     ON Employee.ReportsTo = EmployeeCTE.EmployeeId  
  15. )  
  16. SELECT *  
  17. FROM EmployeeCTE  

In the above example, EmployeeCTE is a Common Type Expression table. And we get the records from the base SELECT statement before UNION ALL. And the result of this query gives you EmployeeId, EmployeeName, Tier WHERE ReportsTo is NULL.

And then the second query executes repeatedly because of the Join with CTE itself. And when the result set of the second query will be final, then the UNION ALL statement executes.

The hard and fast rule is when we’re writing recursive CTE, first of all, we write the NULL part as we do above.

  1. SELECT EmployeeId  
  2.         , FirstName + ' ' + LastName AS EmployeeName  
  3.         , 1 AS Tier  
  4. FROM Employee  
  5. WHERE ReportsTo IS NULL  

And then, we make it UNION ALL with this recursive statement which is a join with CTE itself.

  1. SELECT Employee.EmployeeId  
  2.         , FirstName + ' ' + LastName  
  3.         , Tier + 1  
  4. FROM Employee  
  5. JOIN EmployeeCTE  
  6. ON Employee.ReportsTo = EmployeeCTE.EmployeeId  

Now, let’s see what we get in a single iteration of complete CTE. Let’s see the levels of data.

  1. GO  
  2. WITH EmployeeCTE AS  
  3. (  
  4.     SELECT EmployeeId  
  5.             , FirstName + ' ' + LastName AS EmployeeName  
  6.             , 1 AS Tier  
  7.             , 0 AS LEVEL  
  8.     FROM Employee  
  9.     WHERE ReportsTo IS NULL  
  10. UNION ALL  
  11.     SELECT Employee.EmployeeId  
  12.             , FirstName + ' ' + LastName  
  13.             , Tier + 1  
  14.             , (EmployeeCTE.LEVEL + 1) AS LEVEL  
  15.     FROM Employee  
  16.     JOIN EmployeeCTE  
  17.     ON Employee.ReportsTo = EmployeeCTE.EmployeeId  
  18. )  
  19. SELECT *  
  20. FROM EmployeeCTE  

And when we run this query, we’ll get this result.

CTE In SQL Server 

CTE can make complex subqueries more concise and readable. They are also helpful when you need recursion.

The difference between CTE, temp tables, and Views

Now, you might be confused that CTE is also a temporary table and temp tables also exist standalone in SQL Server. And Views are again temporary tables. So the solution of CTE and temp tables confusion is if we need a large amount of data more than once, then we’ll use #temp tables and if it needs to be recursive or if we need to make something instantly and after executing that, we want only the result and the schema to disappear, we’ll use CTE.

https://dba.stackexchange.com/a/13117

CTE is at least not a table and temp table is a table. And if you're confused about the differences between CTE and Views, you should take a look at this link.


Similar Articles