SQL CTE: How to Master It With Easy Examples

by

[ad_1]

What is the Massive Deal With SQL CTE?

CTE is shorter for frequent desk expressions. And for a single thing, it was 1st released in SQL:1999 specs. So, it is fairly normal. Even a lot more, subqueries and momentary tables are its shut kinfolk.

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

This posting will enable you with that. Alongside with effortless examples, you can master this in no time. It’s a pain-free working experience to understand this nowadays.

But there is much more.

There’s a software you can use to pace up your coding greatly. Well, it’s not that SQL CTE is tough to code. But the resource is just about the corner to kick items 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

Initial, CTE is a temporary named consequence set. So, it has a name, and it’s short-term, like a momentary desk. The result established of a CTE is derived from a Find question. That end result set exists inside of the execution scope of its outer question. And the outer question can be a Select, INSERT, UPDATE, or MERGE. When it is done, the CTE is also long gone. So, with that confined scope, you just can’t reuse a CTE.

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

You also build a CTE using the WITH assertion, like this:

WITH [(column list)] AS ( )

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

Anatomy of a SQL CTE

So, Why Would You Use CTE in SQL?

When summarizing information or computing a complicated system, it is always superior to divide your queries into chunks.

Why?

It simplifies your code. That can make it much easier to go through and realize. And a CTE does that for you. See the higher than sample? It divides the career into 2: the internal question and the outer query. An internal query with 1 CTE is also the simplest.

So, in quick, CTE can support your code chunks be extra readable.

A further purpose to use CTE is when you have to have a hierarchical listing. A recursive CTE can help you with that. You are going to see an illustration of this in a later on part.

For the factors previously mentioned, you can say that a SQL CTE can be recursive or non-recursive.

What It is Not

Now, let’s demystify things about CTE. For the reason that you might have listened to hearsays about it.

First, a non-recursive CTE does not exchange subqueries, derived tables, or short term tables. If you observe the instance before, the purpose is identical to these query standards. But each and every has its put in your SQL scripts. For example, if you will need the momentary end result set in yet another question, a momentary desk can be a much better selection. Mainly because a momentary desk has a bigger scope in your script. You can reference it wherever inside of a series of instructions. It can also have a world-wide scope.

Then, a non-recursive CTE is not for blazing-fast queries. It is not constantly quicker than the alternate options or vice-versa. Check out performance comparisons in this in-depth report.

How to Use SQL CTE?

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

8 Matters That Will Do the job in SQL CTE

1. Use an Inline or External Column Aliases

SQL CTE supports 2 types of column aliases. Underneath is the to start with that uses the inline kind:

USE WideWorldImporters
GO

-- Use an inline column alias
WITH InvoiceCTE AS
(
Pick Month(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Sum
FROM Profits.InvoiceLines il
Inner Sign up for income.Invoices i ON i.InvoiceID = il.InvoiceID
Exactly where i.InvoiceDate In between '1/1/2013' AND '12/31/2013'
Group BY Month(i.InvoiceDate)
)
Select InvoiceMonth, Quantity
FROM InvoiceCTE
Buy BY InvoiceMonth

Employing the AS keyword defines the column alias in a SQL question. In the previously mentioned code, InvoiceMonth and Amount are column aliases.

A further variety of column alias is the external sort. See a revision of the identical code employing it beneath:

USE WideWorldImporters
GO

-- Use an external column alias
WITH InvoiceCTE(InvoiceMonth, Sum)
AS
(
Pick out Month(i.InvoiceDate), SUM(il.ExtendedPrice)
FROM Income.InvoiceLines il
Inner Be a part of sales.Invoices i ON i.InvoiceID = il.InvoiceID
Where i.InvoiceDate Among '1/1/2013' AND '12/31/2013'
Group BY Month(i.InvoiceDate)
)
Find InvoiceMonth, Volume
FROM InvoiceCTE
Order BY InvoiceMonth

This time, the column aliases are defined immediately after the CTE title. The two queries will have the pursuing consequence set:

Invoice Totals per month using SQL CTE.

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

The previous SQL CTE examples you observed stick to a Pick out statement. In addition to a Decide on assertion, you can also use INSERT, UPDATE, DELETE, or MERGE.

Here’s an illustration employing INSERT:

-- Get the most up-to-date product value and include a 2% improve in selling price in products expense record
USE AdventureWorks
GO

DECLARE @productID INT = 703

WITH LatestProductCost AS
(
Decide on Top 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost
FROM Generation.ProductCostHistory pch
In which pch.ProductID = @productID
Purchase BY pch.StartDate DESC
)
INSERT INTO Generation.ProductCostHistory
(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)
Choose
 @productID
,DATEADD(d,1,lpc.EndDate)
,DATEADD(d,366,lpc.EndDate)
,(lpc.StandardCost * .02) + lpc.StandardCost
,GETDATE()
FROM LatestProductCost lpc

3. Various CTEs in a Question

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

-- Having the ahead of and just after merchandise typical charge change
USE AdventureWorks
GO

DECLARE @productID INT = 711

WITH LatestProductCost AS
(
Decide on Major 1 pch.ProductID, pch.StartDate, pch.StandardCost
FROM Manufacturing.ProductCostHistory pch
Where by pch.ProductID = @productID
Buy BY pch.StartDate DESC
),
PreviousProductCost AS
(
Find Leading 1 pch.ProductID, pch.StartDate, pch.StandardCost
FROM Production.ProductCostHistory pch
Inner Be part of LatestProductCost lpc ON pch.ProductID = lpc.ProductID
Exactly 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:

The before and after product cost change.

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:

Result set of a royal family member's ancestor using recursive CTE.

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:

In a batch of SQL statements, an error occurs when the preceding statement is not terminated by a semicolon.

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.

Syntax error occurs if columns in a CTE are not named.

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.

Invalid SQL CTE name reference.

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:

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

Using the CTE snippet in SQL Complete.

Then, rename the CTE.

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

IntelliSense when joining 2 tables in SQL Complete.

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?

[ad_2]