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:

SELECT * FROM Invoice
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.

SELECT Country
, FirstName + ' ' + LastName AS CustomerName
, SUM (Total) AS TotalInvoices
FROM Invoice
JOIN Customer
ON Invoice.CustomerId = Customer.CustomerId
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.

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.

WITH TotalInvoicesByCustomerAndByCountry (Country, CustomerName, TotalInvoices)
AS
(
SELECT Country
, FirstName + ' ' + LastName AS CustomerName
, SUM (Total) AS TotalInvoices
FROM Invoice
JOIN Customer
ON Invoice.CustomerId = Customer.CustomerId
GROUP BY Country, FirstName, LastName
)
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.

WITH TotalInvoicesByCustomerAndByCountry
AS
(
SELECT Country
, FirstName + ' ' + LastName AS CustomerName
, SUM (Total) AS TotalInvoices
FROM Invoice
JOIN Customer
ON Invoice.CustomerId = Customer.CustomerId
GROUP BY Country, FirstName, LastName
),
TopCustomerByCountry
AS
(
SELECT Country
, MAX (TotalInvoices) AS MaxTotalInvoices
FROM TotalInvoicesByCustomerAndByCountry
GROUP BY Country
)
SELECT TotalInvoicesByCustomerAndByCountry.Country
, TotalInvoicesByCustomerAndByCountry.CustomerName
, TopCustomerByCountry.MaxTotalInvoices
FROM TotalInvoicesByCustomerAndByCountry
JOIN TopCustomerByCountry
ON TotalInvoicesByCustomerAndByCountry.Country = TopCustomerByCountry.Country
AND TotalInvoicesByCustomerAndByCountry.TotalInvoices = TopCustomerByCountry.MaxTotalInvoices
ORDER BY TotalInvoicesByCustomerAndByCountry.Country

Look here -- we separate multiple CTEs by comma and remove the arguments from CTEs. We use 1^st CTE in the 2^nd 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.

WITH EmployeeCTE AS
(
SELECT EmployeeId
, FirstName + ' ' + LastName AS EmployeeName
, 1 AS Tier
FROM Employee
WHERE ReportsTo IS NULL
UNION ALL
SELECT Employee.EmployeeId
, FirstName + ' ' + LastName
, Tier + 1
FROM Employee
JOIN EmployeeCTE
ON Employee.ReportsTo = EmployeeCTE.EmployeeId
)
SELECT *
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.

SELECT EmployeeId
, FirstName + ' ' + LastName AS EmployeeName
, 1 AS Tier
FROM Employee
WHERE ReportsTo IS NULL

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

SELECT Employee.EmployeeId
, FirstName + ' ' + LastName
, Tier + 1
FROM Employee
JOIN EmployeeCTE
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.

GO
WITH EmployeeCTE AS
(
SELECT EmployeeId
, FirstName + ' ' + LastName AS EmployeeName
, 1 AS Tier
, 0 AS LEVEL
FROM Employee
WHERE ReportsTo IS NULL
UNION ALL
SELECT Employee.EmployeeId
, FirstName + ' ' + LastName
, Tier + 1
, (EmployeeCTE.LEVEL + 1) AS LEVEL
FROM Employee
JOIN EmployeeCTE
ON Employee.ReportsTo = EmployeeCTE.EmployeeId
)
SELECT *
FROM EmployeeCTE

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

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.