CTE in SQL

Question

What is CTE in database SQL Server and how is different from other types of SQL statements

Jignesh Kumar · Answer

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.