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.
- We started by creating the INNER query which is obtaining customer’s data and numerating the customers by their transaction date.
- 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.