CTE in SQL

Hello Kiran,

A Common Table Expression (CTE) in SQL Server is a temporary result set you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH clause and allows you to simplify complex queries by breaking them down into more manageable parts

Example of CTE :

WITH ExampleCTE AS (
    SELECT EmployeeID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY EmployeeID
)
SELECT EmployeeID, TotalSales
FROM ExampleCTE
WHERE TotalSales > 1000;

In this example, the CTE ExampleCTE calculates the total sales for each employee, and the main query retrieves employees with total sales over 1000. It means you can store and use temporary results in the next statement.

Note : CTE scope is up to the next statement only you can not use CTE out of scope

Scope Limitation: A CTE can be used in a single, subsequent SQL statement, and is not available for use in any other context beyond that statement.

Below is the example of scope limitation,

WITH ExampleCTE AS (
    SELECT EmployeeID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY EmployeeID
)
-- The CTE ExampleCTE is in scope only for the following statement:
SELECT EmployeeID, TotalSales
FROM ExampleCTE
WHERE TotalSales > 1000;

-- Any attempt to reference ExampleCTE beyond this point would result in an error.