Lesson 28 of 54 15 minIntermediate

Common table expressions (WITH)

Name a query and use it like a table: WITH syntax, chaining several CTEs step by step, using one CTE twice, the semicolon rule, scope, and how CTEs compare with derived tables, temp tables and views.

A common table expression (CTE) gives a query a name, at the top of a statement, so the main query can use it like a table. It is a derived table moved to the front, where it is easier to read:

Query.sql

1WITH OrderTotals AS (2    SELECT OrderId, SUM(Quantity * UnitPrice) AS Total3    FROM dbo.OrderItems4    GROUP BY OrderId5)6SELECT o.OrderId, c.FirstName, t.Total7FROM OrderTotals AS t8JOIN dbo.Orders    AS o ON o.OrderId    = t.OrderId9JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId10WHERE t.Total > 10011ORDER BY t.Total DESC;

Output

OrderId  FirstName   Total
-------  ---------  ------
      4  Dev        524.00
      7  Dev        199.50
      5  Farid      120.50
      2  Ben        113.00
      3  Asha       112.80
      8  Zoë        102.50

Read it top to bottom: first work out each order’s total, then use those totals. With a derived table, the first step would be buried in the middle of the FROM clause.

Several CTEs, one step at a time

Separate several CTEs with commas. Each one can use the ones above it, so a complicated question becomes a series of small, named steps:

Query.sql

1WITH OrderTotals AS (2    SELECT OrderId, SUM(Quantity * UnitPrice) AS Total3    FROM dbo.OrderItems4    GROUP BY OrderId5),6CustomerTotals AS (7    SELECT o.CustomerId, COUNT(*) AS Orders, SUM(t.Total) AS Spent8    FROM OrderTotals AS t9    JOIN dbo.Orders AS o ON o.OrderId = t.OrderId10    WHERE o.Status <> 'Cancelled'11    GROUP BY o.CustomerId12),13Average AS (14    SELECT AVG(Spent) AS AvgSpent FROM CustomerTotals15)16SELECT c.FirstName, ct.Orders, ct.Spent, a.AvgSpent17FROM CustomerTotals AS ct18JOIN dbo.Customers AS c ON c.CustomerId = ct.CustomerId19CROSS JOIN Average AS a20WHERE ct.Spent > a.AvgSpent21ORDER BY ct.Spent DESC;

Output

FirstName  Orders   Spent    AvgSpent
---------  ------  ------  ----------
Dev             2  723.50  263.930000

Interactive

A query as named steps

CustomerTotals.sql

1SELECT o.CustomerId, COUNT(*) AS Orders, SUM(t.Total) AS Spent2FROM OrderTotals AS t3JOIN dbo.Orders AS o ON o.OrderId = t.OrderId4WHERE o.Status <> 'Cancelled'5GROUP BY o.CustomerId

	CustomerId	Orders	Spent
1	1	2	150.80
2	2	2	131.00
3	4	2	723.50
4	6	2	211.85
5	8	1	102.50

Step 2 reads step 1 as if it were a table, drops Chloe’s cancelled order, and adds up each customer’s orders. Customers without orders never appear here.

Naming the columns

You can name a CTE’s columns in brackets after its name instead of with aliases inside. Every column needs a name one way or the other:

Query.sql

1WITH MonthlyOrders (Month, Orders) AS (2    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1), COUNT(*)3    FROM dbo.Orders4    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)5)6SELECT Month, Orders7FROM MonthlyOrders8WHERE Orders >= 29ORDER BY Month;

Output

Month       Orders
----------  ------
2024-02-01       2
2024-03-01       2
2024-04-01       2
2024-05-01       2
2024-06-01       2

Using a CTE twice

A CTE can be referenced more than once in the main query, which a derived table cannot without being written out again. Comparing each month with the month before means joining the monthly figures to themselves:

Query.sql

1WITH Monthly AS (2    SELECT MONTH(o.OrderDate) AS M, SUM(oi.Quantity * oi.UnitPrice) AS Revenue3    FROM dbo.Orders AS o4    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId5    GROUP BY MONTH(o.OrderDate)6)7SELECT cur.M AS Month, cur.Revenue, prev.Revenue AS PrevMonth,8       cur.Revenue - prev.Revenue AS Change9FROM Monthly AS cur10LEFT JOIN Monthly AS prev ON prev.M = cur.M - 111ORDER BY cur.M;

Output

Month  Revenue  PrevMonth   Change
-----  -------  ---------  -------
    2   151.00       NULL     NULL
    3   636.80     151.00   485.80
    4   134.10     636.80  -502.70
    5   302.00     134.10   167.90
    6   109.35     302.00  -192.65

The semicolon rule

WITH has other meanings in T-SQL (such as table hints), so the statement before a CTE must end with a semicolon:

Query.sql

1SELECT COUNT(*) AS Orders FROM dbo.Orders2WITH Recent AS (SELECT * FROM dbo.Orders WHERE OrderDate >= '2024-06-01')3SELECT OrderId FROM Recent;

Output

Msg 336, Level 15, State 1, Line 2
Incorrect syntax near 'Recent'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

That is why you often see CTEs written as ;WITH. Ending every statement with a semicolon, as this course does, avoids the problem.

A CTE lives for one statement

A CTE exists only for the single statement that follows it. The next statement cannot see it:

Query.sql

1WITH Pending AS (SELECT OrderId FROM dbo.Orders WHERE Status = 'Pending')2SELECT COUNT(*) AS PendingOrders FROM Pending;3 4SELECT * FROM Pending;

Output

PendingOrders
-------------
            2

Msg 208, Level 16, State 1, Line 4
Invalid object name 'Pending'.

Changing data through a CTE

The statement after a CTE can be an UPDATE or DELETE, which changes the underlying table. This is a clear way to say “find these rows, then change them”:

Query.sql

1WITH OutOfStock AS (2    SELECT ProductId, Name, Stock3    FROM dbo.Products4    WHERE Stock = 05)6UPDATE OutOfStock SET Stock = 25;7 8SELECT Name, Stock FROM dbo.Products WHERE ProductId IN (9, 12);

Output

Name              Stock
----------------  -----
Wall plugs (100)     25
Safety goggles       25

CTE, derived table, temp table or view?

	Lives for	Reusable	Use it for
Derived table	one query	no	a small one-off step inside FROM
CTE	one statement	within that statement	readable step-by-step queries, recursion
Temp table `#t`	the session	by later statements	a result that is expensive or needed many times; it can be indexed
View	until dropped	by anyone	a query the whole team uses (the Views module)

Quick check

A batch has two statements after a CTE definition. Which of them can use the CTE?

Quick check

SELECT 1 AS x is followed on the next line by WITH t AS (…) SELECT …, and the batch fails. Why?

Practice

Using two CTEs, list each category name with its share of total revenue as a percentage (one decimal place). The first CTE should total revenue per category; the second the grand total. Include only categories that have sales, highest share first.

Show a hint

CategoryRevenue: join OrderItems to Products, GROUP BY CategoryId. Total: SELECT SUM(Revenue) FROM CategoryRevenue. Then join Categories and CROSS JOIN Total.

Show the solution

Solution.sql

1WITH CategoryRevenue AS (2    SELECT p.CategoryId, SUM(oi.Quantity * oi.UnitPrice) AS Revenue3    FROM dbo.OrderItems AS oi4    JOIN dbo.Products AS p ON p.ProductId = oi.ProductId5    GROUP BY p.CategoryId6),7Total AS (8    SELECT SUM(Revenue) AS AllRevenue FROM CategoryRevenue9)10SELECT c.Name, cr.Revenue,11       CAST(cr.Revenue * 100 / t.AllRevenue AS decimal(4,1)) AS PctOfRevenue12FROM CategoryRevenue AS cr13JOIN dbo.Categories AS c ON c.CategoryId = cr.CategoryId14CROSS JOIN Total AS t15ORDER BY cr.Revenue DESC;

Output

Name      Revenue  PctOfRevenue
--------  -------  ------------
Timber     689.60          51.7
Building   253.00          19.0
Tools      155.25          11.6
Paint      120.50           9.0
Fixings    101.30           7.6
Safety      13.60           1.0

Remember

WITH name AS (query) names a query for the statement that follows.
Chain CTEs with commas; each can use the ones above it.
A CTE can be referenced several times in its statement.
The statement before WITH must end with a semicolon.
A CTE lasts for one statement and is not stored; use a temp table for reuse.
UPDATE and DELETE can target a CTE to change the underlying table.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Common table expressions (WITH)

Several CTEs, one step at a time

A query as named steps

Naming the columns

Using a CTE twice

The semicolon rule

A CTE lives for one statement

Changing data through a CTE

CTE, derived table, temp table or view?

Remember