SQL CTE: How to Master It With Easy Examples - DZone Database

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.

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)

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'

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

3. Multiple CTEs in a Query

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

INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductID

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

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:

    SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost

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)

)

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

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

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:

    -- Second recursive member returns female ancestors of the previous generation

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

​

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:

            INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID

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

(

Other Things Not Allowed in a SQL CTE

• When these keywords are found in a recursive member:

  • 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

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?