WHAT IS CTE – COMMON TABLE EXPRESSION IN MSSQL

In Microsoft SQL Server, a Common Table Expression (CTE) is a temporary result set that you can reference within the context of a SELECT, INSERT, UPDATE, or DELETE statement.

CTEs allow you to create derived tables within a query, making the SQL code more readable and modular. With CTE we can create a subset of data with specific criteria before using it in the main query.

CTEs are a mechanism to store the query and it’s results in memory temproarily on a batch level. CTEs are applicable for faster access to data for quick comparisons and recursive operations.

In real world, the CTEs can be used to avoid self joins and subqueries.

Here is the basic syntax for a CTE in T-SQL (Transact-SQL):

Syntax:

WITH CTE_Name 
AS
(
    -- CTE definition
    SELECT Column1, Column2, ...
    FROM YourTableName
    WHERE SomeCondition
)

-- Query that references the CTE
SELECT *
FROM CTE_Name;

Simple example of CTEs

Example 1: Simple select using CTEs

WITH Purchase_CTE AS
(SELECT ProductID, 
		LineTotal,
		ReceivedQty
  FROM [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail])
SELECT * FROM Purchase_CTE;

Example 2: How to insert using a CTE

WITH CTE_UnitMeasure AS
(SELECT [UnitMeasureCode]
      ,[Name]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[Production].[UnitMeasure])
INSERT INTO CTE_UnitMeasure
VALUES ('TE',
	'Test1',
	SYSDATETIME());

Advanced SQL use cases of CTEs

Common Table Expressions (CTEs) in SQL are often used in real-time scenarios where complex queries or data transformations are required. Here are some common use cases for CTEs:

Example 3: Using CTE in Recursive queries and self joins

• CTEs can be used to perform recursive queries, such as traversing hierarchical data structures like organizational charts or bill of materials. This type of CTE calls the same CTE inside it.
• To implement recursive CTE, we need an ANCHOR ELEMENT [BASE DATA, START VALUE] & TERMINATION CHECK
• The classic example of CTEs in recursive queries is to find the managers of employees.
• Let’s assume that our requirement is to report list of all managers in the hierarchy for a given employee with the user id = 7

--Let's create a sample user table with userid, managerid and username.

CREATE TABLE [dbo].[Users](
	[userid] [int] NOT NULL,
	[username] [nvarchar](50) NOT NULL,
	[managerid] [int] NULL
);

GO
--Insert some base data 
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (1, 'James', NULL)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (2, 'John', 1)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (3, 'Mark', 2)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (4, 'Lusia', 2)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (5, 'Lynn', 1)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (6, 'Vince', 5)
INSERT [dbo].[Users] ([userid], [username], [managerid]) VALUES (7, 'Claire', 6)

--Check the table
SELECT * FROM [Users]


--Write our CTE
WITH UserCTE AS 
	(
  SELECT userId, 
         userName, 
         managerId, 
         0 AS steps -- This is the Anchor element, which is the base or start value
  FROM dbo.Users
  WHERE userId = 7
    
  UNION ALL			-- TO COMBINE THE RESULT, IN ORDER
  
  SELECT mgr.userId, 
         mgr.userName, 
         mgr.managerId, 
         usr.steps +1 AS steps
  FROM UserCTE AS usr
  INNER JOIN dbo.Users AS mgr	-- SELF JOIN
  ON usr.managerId = mgr.userId	-- TERMINATION CHECK
	)
SELECT * FROM UserCTE;

The above example checks who is the manager for the user with the id 7 and it checks further the manager of that person and so on, until the termination check fails.

The output of this query would be,

Example 4: Using CTEs for Data Aggregations, Data Validation and Data Cleansing

• CTEs are useful for transforming and aggregating data before using it in subsequent parts of a query.
• CTEs are handy for validating data against certain conditions or business rules before using it in further processing, inorder to check the data integrity issues before performing updates or inserts.
• CTEs can assist in data cleansing tasks by identifying and handling anomalies or outliers to remove or correct inconsistent data before processing.
• CTEs makes the code look cleaner.

Here is an example of one other use of CTE, where we join 2 tables, perform the aggregations, check if data is not null etc in our WITH clause.

--LineTotal greater than 10000
WITH ProductSales AS
(SELECT    SalesOrderID
	  ,Name AS Productname
	  ,SUM(ISNULL(LineTotal, 0)) AS LineTotal
FROM [Sales].[SalesOrderDetail] s
JOIN [Production].[Product] p
ON   s.ProductID = p.ProductID
GROUP BY SalesOrderID
	 ,Name)
SELECT * 
FROM ProductSales
WHERE LineTotal >= 10000;

Example 5: Using multiple CTEs for Derived Tables, Complex Joins and Filtering and Dynamic SQL Generation

• CTEs can help enrich data by joining it with other tables or datasets. For example, we can join data with lookup tables to replace codes with descriptive values.
• CTEs can be part of a larger query-building process with multiple WITH clauses, especially when generating dynamic SQL, by building a dynamic query to retrieve data based on user-specified parameters.
• CTEs simplify complex join operations and filtering conditions, making the query more maintainable.

Let us see another example on how we can use multiple CTEs

--LineTotal of Products with reviews greater than 4
WITH ProductSales AS
(SELECT s.SalesOrderID
	   ,p.ProductID
	  ,p.Name AS Productname
	  ,SUM(ISNULL(LineTotal, 0)) AS LineTotal
FROM [Sales].[SalesOrderDetail] s
JOIN [Production].[Product] p
ON   s.ProductID = p.ProductID
GROUP BY s.SalesOrderID
	    ,p.Name
		,p.ProductID)
--With statement 2 to get the Productreview
,Productreview AS
(SELECT [ProductID]
      ,[ReviewerName]
      ,[Rating]
      ,[Comments]
  FROM [Production].[ProductReview]
  WHERE [Rating] >= 4)

SELECT *
FROM ProductSales a
JOIN Productreview b
ON a.ProductID = b.ProductID
;

The above query has 2 WITH statements with different operations and the SELECT query outside look much cleaner where it joins the CTEs.

The output of the above query will look like,

Example 6: Using CTEs for Calculating Running Totals, Time Series Analysis and Window Functions:

• CTEs can be applied to implement pagination or ranking of results, especially when using the ROW_NUMBER() or RANK() window functions. For example, calculating average sales for each product category using window functions.
• CTEs are useful for calculating running totals or cumulative aggregates over an ordered set of data and time-based calculations. For example, calculating the running total of sales over time.

Let us see an example of how Windows functions are used with CTEs

-- Sample Sales table
CREATE TABLE Sales (
    Date DATE,
    Amount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO Sales (Date, Amount) VALUES
('2023-01-01', 100),
('2023-01-02', 150),
('2023-01-03', 200),
('2023-01-04', 75);

-- Calculate running total using CTE
WITH RunningTotalCTE AS (
    SELECT
        Date,
        Amount,
        SUM(Amount) OVER (ORDER BY Date) AS RunningTotal
    FROM
        Sales
)
SELECT
    Date,
    Amount,
    RunningTotal
FROM
    RunningTotalCTE;