SQL CTE: How to Master It in One Sitting With Easy Examples

What’s the Big Deal With SQL CTE?

CTE is short for common table expressions. And for one thing, it was first introduced in SQL:1999 specifications. So, it’s pretty standard. Even more, subqueries and temporary tables are its closed relatives.

But What Is SQL CTE? And How Do You Use It?

This article will help you with that. Along with easy examples, you can master this in no time. It’s a painless experience to learn this today.

But there’s more.

There’s a tool you can use to speed up your coding drastically. Well, it’s not that SQL CTE is hard to code. But the tool is just around the corner to kick things up.

So, why not start it now? Let’s go!

What Is SQL CTE?

Let’s describe it by telling you what it is and what it is not.

What It Is

First, CTE is a temporary named result set. So, it has a name, and it’s temporary, like a temporary table. The result set of a CTE is derived from a SELECT query. That result set exists within the execution scope of its outer query. And the outer query can be a SELECT, INSERT, UPDATE, or MERGE. When it’s done, the CTE is also gone. So, with that limited scope, you can’t reuse a CTE.

A CTE can also reference itself. And when it does, it becomes a recursive common table expression.

You also create a CTE using the WITH statement, like this:

WITH <cte_name>[(column list)]
AS
(
   <inner query defining the CTE>
)
<outer query: SELECT | INSERT | UPDATE | DELETE | MERGE>

To illustrate, see the anatomy of a SQL CTE below:

So, Why Would You Use CTE in SQL?

When summarizing data or computing a complex formula, it’s always good to divide your queries into chunks.

Why?

It simplifies your code. That makes it easier to read and understand. And a CTE does that for you. See the above sample? It divides the job into 2: the inner query and the outer query. An inner query with 1 CTE is also the simplest.

So, in short, CTE can help your code chunks be more readable.

Another reason to use CTE is when you need a hierarchical list. A recursive CTE can help you with that. You’ll see an example of this in a later section.

For the reasons above, you can say that a SQL CTE can be recursive or non-recursive.

What It is Not

Now, let’s demystify stuff about CTE. Because you may have heard hearsays about it.

First, a non-recursive CTE does not replace subqueries, derived tables, or temporary tables. If you notice the example earlier, the purpose is similar to these query standards. But each has its place in your SQL scripts. For example, if you need the temporary result set in another query, a temporary table can be a better choice. Because a temporary table has a larger scope in your script. You can reference it anywhere within a series of commands. It can also have a global scope.

Then, a non-recursive CTE is not for blazing-fast queries. It is not always faster than the alternatives or vice-versa. Check out performance comparisons in this in-depth article.

How to Use SQL CTE?

Now that you know what it is and what it is not, it’s time to know how to use it. Let’s also divide it into 2: what will work and what will not work.

8 Things That Will Work in SQL CTE

1. Use an Inline or External Column Aliases

SQL CTE supports 2 forms of column aliases. Below is the first that uses the inline form:

USE WideWorldImporters;
GO

-- Use an inline column alias
WITH InvoiceCTE AS 
(
	SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Amount
	FROM Sales.InvoiceLines il
	INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID
	WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
	GROUP BY MONTH(i.InvoiceDate)
)
SELECT InvoiceMonth, Amount 
FROM InvoiceCTE
ORDER BY InvoiceMonth;

Using the AS keyword defines the column alias in a SQL query. In the above code, InvoiceMonth and Amount are column aliases.

Another form of column alias is the external form. See a revision of the same code using it below:

USE WideWorldImporters;
GO

-- Use an external column alias
WITH InvoiceCTE(InvoiceMonth, Amount) 
AS 
(
	SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)
	FROM Sales.InvoiceLines il
	INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID
	WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
	GROUP BY MONTH(i.InvoiceDate)
)
SELECT InvoiceMonth, Amount 
FROM InvoiceCTE
ORDER BY InvoiceMonth;

This time, the column aliases are defined after the CTE name. Both queries will have the following result set:

2. SELECT, INSERT, UPDATE, DELETE, or MERGE Follows a SQL CTE

The previous SQL CTE examples you saw follow a SELECT statement. Besides a SELECT statement, you can also use INSERT, UPDATE, DELETE, or MERGE.

Here’s an example using INSERT:

-- Get the latest product cost and add a 2% increase in price in product cost history
USE AdventureWorks;
GO

DECLARE @productID INT = 703;

WITH LatestProductCost AS 
(
	SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost
	FROM Production.ProductCostHistory pch
	WHERE pch.ProductID = @productID
	ORDER BY pch.StartDate DESC
)
INSERT INTO Production.ProductCostHistory 
(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)
SELECT 
 @productID
,DATEADD(d,1,lpc.EndDate)
,DATEADD(d,366,lpc.EndDate)
,(lpc.StandardCost * 0.02) + lpc.StandardCost
,GETDATE()
FROM LatestProductCost lpc;

3. Multiple CTEs in a Query

You can also define more than 1 CTE in a query. Here’s an example:

-- Getting the before and after product standard cost change
USE AdventureWorks;
GO

DECLARE @productID INT = 711;

WITH LatestProductCost AS 
(
	SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost
	FROM Production.ProductCostHistory pch
	WHERE pch.ProductID = @productID
	ORDER BY pch.StartDate DESC
),
PreviousProductCost AS 
(
	SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost
	FROM Production.ProductCostHistory pch
	INNER JOIN LatestProductCost lpc ON pch.ProductID = lpc.ProductID
	WHERE pch.ProductID = @productID
	  AND pch.StartDate < lpc.StartDate
    ORDER BY pch.StartDate DESC
)
SELECT 
 lpc.ProductID
,p.Name AS Product
,lpc.StandardCost AS LatestCost
,lpc.StartDate
,ppc.StandardCost AS PreviousCost
FROM LatestProductCost lpc
INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductID
INNER JOIN Production.Product p ON lpc.ProductID = p.ProductID
WHERE lpc.ProductID = @productID;

Multiple CTEs are separated by commas. You can see 2 CTEs in the above example. And it will have the following result set:

4. Reference a SQL CTE Several Times

But there’s more to the previous example. The PreviousProductCost CTE references the LatestProductCost CTE. Then, the outer query referred to LatestProductCost CTE again. You can reference a CTE like this several times as needed.

5. Use SQL CTE in a Stored Procedure and Pass Arguments to It

You can also use SQL CTE in a stored procedure. Then, passing stored procedure parameter values to it is possible.

Here’s an example:

USE AdventureWorks;
GO

IF OBJECT_ID('dbo.uspInsertNewProductCost') IS NOT NULL
	SET NOEXEC ON
GO
CREATE PROCEDURE dbo.uspInsertNewProductCost
(
	@productID INT,
	@increase DECIMAL(3,2)
)
AS
SET NOCOUNT ON;

WITH LatestProductCost AS 
(
	SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost
	FROM Production.ProductCostHistory pch
	WHERE pch.ProductID = @productID
	ORDER BY pch.StartDate DESC
)
INSERT INTO Production.ProductCostHistory 
(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)
SELECT 
 @productID
,DATEADD(d,1,lpc.EndDate)
,DATEADD(d,366,lpc.EndDate)
,(lpc.StandardCost * @increase) + lpc.StandardCost
,GETDATE()
FROM LatestProductCost lpc;

GO

In the above, a CTE is used to receive 2 stored procedure parameters , @productID, and @increase. This will add a new row in the ProductCostHistory table.

6. Use SQL CTE in a View

You can also use SQL CTE in a view. Here’s an example:

USE WideWorldImporters;
GO

CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProduct
AS

WITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount) 
AS 
(
	SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)
	FROM Sales.InvoiceLines il
	INNER JOIN Sales.Invoices i ON i.InvoiceID = il.InvoiceID
	GROUP BY YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID
)
SELECT i.InvoiceYear, i.InvoiceMonth, si.StockItemName,  i.Amount 
FROM InvoiceCTE i
INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID
GO

7. Use SQL CTE in a Cursor

You can also use SQL CTE with a cursor. Then, loop through the results. Here’s an example:

USE WideWorldImporters
GO

DECLARE @invoiceMonth TINYINT
DECLARE @amount MONEY

DECLARE invoice_cursor CURSOR FOR

	WITH InvoiceCTE AS 
	(
		SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Amount
		FROM Sales.InvoiceLines il
		INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID
		WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
		GROUP BY MONTH(i.InvoiceDate)
	)
	SELECT InvoiceMonth, Amount 
	FROM InvoiceCTE
	ORDER BY InvoiceMonth

OPEN invoice_cursor
FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount
WHILE @@fetch_status = 0 
BEGIN  
	PRINT 'Invoice Month: ' + CAST(@invoiceMonth AS VARCHAR)
	PRINT 'Amount: ' + CAST(@amount AS VARCHAR)

	FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount
END

CLOSE invoice_cursor
DEALLOCATE invoice_cursor

8. Use a Temporary Table in a Recursive CTE

A recursive CTE has an anchor member and a recursive member. You can use this to query hierarchical data. A family tree, for example, is hierarchical by nature.

It doesn’t matter if the CTE uses a usual table or a temporary table. See an example using a temporary table below:

-- British Royal family
CREATE TABLE dbo.RoyalFamily
(
	ID INT NOT NULL,
	Name VARCHAR(60) NOT NULL,
	Father INT,
	Mother INT
	CONSTRAINT PK_RoyalFamily_ID PRIMARY KEY (ID)
)
GO

INSERT INTO dbo.RoyalFamily
(ID, Name, Father, Mother)
VALUES
(1,'Philip',NULL,NULL),
(2,'Queen Elizabeth II',NULL,NULL),
(3,'Charles',1,2),
(4,'Anne',2,1),
(5,'Andrew',2,1),
(6,'Edward',2,1),
(7,'Diana',NULL,NULL),
(8,'Camilla',NULL,NULL),
(9,'Mark Philips',NULL,NULL),
(10,'Timothy Laurence',NULL,NULL),
(11,'Sarah',NULL,NULL),
(12,'Sophie',NULL,NULL),
(13,'William',3,7),
(14,'Harry',3,7),
(15,'Peter Philips',9,4),
(16,'Zara Tindall',9,4),
(17,'Beatrice',5,11),
(18,'Eugenie',5,11),
(19,'Louise',6,12),
(20,'James',6,12),
(21,'Catherine',NULL,NULL),
(22,'Meghan',NULL,NULL),
(23,'Autumn Philips',NULL,NULL),
(24,'Mike Tindall',NULL,NULL),
(25,'Jack Brooksbank',NULL,NULL),
(26,'George',13,21),
(27,'Charlotte',13,21),
(28,'Louis',13,21),
(29,'Archie Harrison Mountbatten-Windsor',14,22),
(30,'Savannah',15,23),
(31,'Isla',15,23),
(32,'Mia Grace',24,16),
(33,'Lena',24,16);

DECLARE @id INT = 26;  -- Prince George

WITH Ancestor(ID) AS 
(
    -- First anchor member returns the royal family member in question
	SELECT ID 
	FROM dbo.RoyalFamily 
	WHERE ID = @id
	UNION
	-- Second anchor member returns the father
	SELECT Father
	FROM dbo.RoyalFamily 
	WHERE ID = @id
	UNION
	-- Third anchor member returns the mother
	SELECT Mother
	FROM dbo.RoyalFamily
	WHERE ID = @id
	UNION ALL
	-- First recursive member returns male ancestors of the previous generation
	SELECT rf.Father
	FROM RoyalFamily rf
	INNER JOIN Ancestor a ON rf.ID = a.ID
	UNION ALL
	-- Second recursive member returns female ancestors of the previous generation
	SELECT rf.Mother
	FROM RoyalFamily rf
	INNER JOIN Ancestor a ON rf.ID = a.ID
)
SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.Father
FROM RoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.ID
ORDER BY rf.ID DESC

Below is the output of the query above:

Here’s what happened in this query:

• Mother and Father columns are IDs of a royal family member.

• Prince George (ID = 26) showed on top. It is the first anchor member of the CTE.

• His mother is Catherine (ID = 21), and his father is Prince William (ID = 13). These are also the second and third anchor members.

• Then, Prince William’s parents are Princess Diana (ID = 7) and Prince Charles (ID = 3). This and the next bullet point are part of the recursive members of the CTE.

• Meanwhile, Prince Charles’ parents are Queen Elizabeth (ID = 2) and Prince Philip (ID = 1).

But take note: an incorrectly written recursive CTE may cause an infinite loop. So, to protect you from that, you can add MAXRECURSION n, where n is the number of loops. Add this at the end of the query after the WHERE clause or the last JOIN.

Great! We used SQL CTE to get the hierarchical list of the British royal family.

4 Things That Won’t Work in SQL CTE

There are rules in SQL Server CTE. So, in this section, we’ll discuss the things that won’t work.

Let’s begin.

1. No Semicolon Before the WITH Clause

At times you will encounter a syntax error if the WITH clause of a CTE is not preceded by a semicolon. This happens when you run a batch of SQL statements. Here’s an example:

This happens because the WITH clause is used for other purposes like a table hint. Adding a semicolon in the preceding statement will fix the problem.

Depending on your editor, squiggly lines also appear under a CTE name like the one you see above. The error message is clear enough on how to fix it.

2. SQL CTE Column Conflicts

You will have problems if

• the number of columns in both anchor and recursive members is different.

• columns are not named

• there are duplicate names

• data types of columns are not the same for both anchor and recursive members.

Here’s an example of CTE syntax errors because of unnamed columns.

3. Reusing a SQL CTE Name Outside of the Outer Query

A SQL CTE is not reusable. I’ve mentioned this earlier, but I like to stress the point further. Based on the earlier sample, you can’t reference the InvoiceCTE in the next SQL command. It will trigger an error.

If you need the temporary result set in another query in a batch, there are a few options. One is a temporary table. Or use multiple non-recursive CTE if this is faster.

4. Nesting a SQL CTE

Nested CTEs will simply not work. Here’s an example that will cause several syntax errors:

WITH InvoiceCTE(InvoiceMonth, StockItemID, Amount) 
AS 
(
	SELECT MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)
	FROM Sales.InvoiceLines il
	INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID
	WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
	GROUP BY MONTH(i.InvoiceDate), il.StockItemID
),
AverageAmountPerMonth AS
(
	SELECT InvoiceMonth, AVG(Amount) AS Average
	FROM (WITH InvoiceAmountPerMonth 
	      AS
		  (
			SELECT i.InvoiceMonth, si.StockItemName,  i.Amount 
			FROM InvoiceCTE i
			INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID
		  )
         )
)
SELECT * FROM AverageAmountPerMonth;

Other Things Not Allowed in a SQL CTE

• When these keywords are found in a recursive member:

  • TOP

  • LEFT, RIGHT, and OUTER JOIN (But INNER JOIN is allowed)

  • GROUP BY and HAVING

  • Subqueries

  • SELECT DISTINCT

• Use of scalar aggregation

• Use of SELECT INTO, OPTION clause with query hints, and FOR BROWSE.

• ORDER BY without a TOP clause

Pro Coding Tips for SQL CTE

Typing all the codes above without IntelliSense can be difficult and error-prone. So, if you could minimize these and speed up coding by up to 4 times faster, why not take it? That’s why there’s SQL Complete by Devart. It’s a SQL Server Management Studio smart add-in. It delivers SQL IntelliSense, autocompletion, refactoring, formatting, and debugging.

Let’s see how it works with SQL CTE.

First, in the SSMS Query window, type cte and press Tab. This code snippet will give you a CTE template you can fill up. See below.

Then, rename the CTE.

Then, edit the CTE until the code is like this:

WITH InvoiceCTE(InvoiceMonth, Amount) 
AS 
(
	SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)
	FROM Sales.InvoiceLines il
	INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID
	WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
	GROUP BY MONTH(i.InvoiceDate)
)
SELECT InvoiceMonth, Amount 
FROM InvoiceCTE
ORDER BY InvoiceMonth;

Make use of the table suggestions and use snippets like ij for INNER JOIN. SQL Complete will suggest the likely joined table and columns. So, make use of those.

See for yourself. Start with joining the 2 tables.

Then, add columns using the column picker.

SQL CTE is another way of expressing queries with a temporary result set. This standard improves the readability of code.

So, why not try SQL CTE today with SQL Complete?