Common Table Expressions (CTE) In Sql Server

How to use Common Table Expressions (CTE) in Sql Server

Common table expressions (CTEs) are temporary structures, that are similar to sub-queries with the exception that they can self reference; they are defined as objects that are visible only to the statement that defines them.


1. Common table Expression Syntax Syntax

CTE syntax consist in the following structure

  • The inner query
  • The outer query

CTE in sql server structure is as follows:

WITH C1 as (
-- INNER QUERY
SELECT …
FROM T1 
WHERE  …
)
--OUTER QUERY
SELECT …
FROM C1 
WHERE …

You can nest CTEs if you want by using the following structure.

WITH C1 as (
-- INNER QUERY
SELECT …
FROM T1 
WHERE  …
),C2 AS(
-- SECOND INNER QUERY
SELECT …
FROM T2 
WHERE  …
)
--OUTER QUERY
SELECT …
FROM C1 
WHERE …

Some considerations that you have to take

  • CTEs inner queries are temporal, after CTE execution is not possible to access them, unless you execute the query again.
  • You have to end your CTE statement with ‘;’ to avoid errors.
  • CTEs make your queries more readable.
  • You can’t order CTE inner queries.
  • A CTE can be recursive.

There are several things that you can do with a CTE.

2. Common table expression examples

For these examples we are going to use WideWorldImporters Database.

A) Using a Window Functions with a Common Table Expression in Sql Server

The following query is going to return data the last customer’s transaction ordered by date.

WITH C AS(
SELECT SC.CustomerName
      ,CT.CustomerTransactionID
	  ,TransactionAmount
	  ,ROW_NUMBER() OVER(PARTITION BY SC.CustomerID ORDER BY TransactionDate) as RN
FROM [Sales].[CustomerTransactions] CT
INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID 
)
SELECT CustomerName
	  ,CustomerTransactionID
	  ,TransactionAmount
FROM C
WHERE RN = 1
ORDER BY CustomerName;

INNER CTE query

OUTER CTE query

In this example we followed the next steps.

  1. We started by creating the INNER query which is obtaining customer’s data and numerating the customers by their transaction date.
  2. Then we created the OUTER query and filter the RN obtaining the last transaction done by each customer.

This query can be also be used for deleting duplicate rows.


1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *