SQL WITH CTE

In SQL Server, WITH CTE defines a temporary named result set.

This result set is called a common table expression (CTE).

CTEs can make queries more readable and more efficient in execution.

Example

#

List annual sales with the best years first.

WITH Cte (Year, Count, Amount) AS (
  SELECT YEAR(OrderDate) AS Year,
         COUNT(Id) AS Count,
         SUM(TotalAmount) AS Amount
    FROM [Order]
   GROUP BY YEAR(OrderDate)
)

SELECT Year, Amount 
  FROM Cte
 ORDER BY Amount DESC
Result:  3 records
Year TotalSales
2013 658388.75
2014 469771.34
2012 226298.50

Using WITH CTE

#

WITH CTE defines a temporary named result called a CTE.

A CTE is a query that can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Instead of subqueries, CTEs are used to make the query more readable and efficient in execution.

A query can use multiple CTEs (see below).


Syntax

Syntax for the WITH CTE function.

WITH name (column1, column2, ..., columnN) AS (
  SELECT statement
)

name -- the name of the CTE which is referred in a subsequent query.

column1, ... -- column names representing the columns in the SELECT statement.

SELECT statement -- a query used to populate the CTE result set.


More Examples

WITH CTE with COLUMN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the smallest and largest orders by month in 2013.
WITH Cte (Month, Min, Max) AS (
  SELECT MONTH(OrderDate) AS Month,
         MIN(TotalAmount) AS Min,
         MAX(TotalAmount) AS Max
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY MONTH(OrderDate)
)

SELECT Month, 
       Min AS MinOrder,
       Max AS MaxOrder
  FROM Cte
 ORDER BY Month
Result:  12 records
Month MinOrder MaxOrder
1 49.80 11493.20
2 174.90 5793.10
3 147.00 10495.60
4 136.80 10588.50
5 110.00 10191.70

MULTIPLE NAMED CTEs with SELECT JOIN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List customers in the US and their order counts and order amounts for 2013.
WITH CteOrder (CustomerId, OrderCount, OrderAmount) AS (
  SELECT CustomerId,
         COUNT(Id),
         SUM(TotalAmount)
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY CustomerId
),
CteCustomer (Id, FirstName, LastName) AS (
  SELECT Id, FirstName, LastName
    FROM Customer
   WHERE Country = 'USA'
)

SELECT FirstName, LastName, OrderCount, OrderAmount
  FROM CteOrder O
  JOIN CteCustomer C ON O.CustomerId = C.Id
 ORDER BY LastName
Result:  13 records
FirstName LastName OrderCount OrderAmount
Art Braunschweiger 2 2972.50
Karl Jablonski 8 10262.55
Yoshi Latimer 3 2283.20
Helvetius Nagy 2 1333.30
Liz Nixon 2 2955.40

You may also like



Guides


vsn 3.1