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 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.