A common table expression (CTE) is a named temporary result set that may be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used as part of a SELECT query in a CREATE VIEW.
CTEs, like database views and derived tables, make it easier to develop and maintain complex queries by increasing readability and simplifying them. This decrease in complexity is accomplished by dissecting normally complex queries into simple components that can be used and reused in recreating the query.
When you need to refer to a derived table many times in a single query, when you don’t want to create a view in the database, or when you want to execute the same calculation across multiple query components, you’ll utilize CTE.
In simple words, CTE is a temporary result set that can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax:
WITH expression_name [ ( column_name [,…n] ) ]
AS
( CTE_Query_Definition )
Example:
WITH CTE_Emp AS(
SELECT
EName AS EmployeeName,
SUM(Sal + (IIF(Comm IS NULL OR Comm<100, ISNULL(Comm,0)+100, Comm))) AS CurrentPackage,
SUM(Sal + (IIF(Comm IS NULL OR Comm<100, ISNULL(Comm,0)+100, Comm))) +
SUM(Sal + (IIF(Comm IS NULL OR Comm<100, ISNULL(Comm,0)+100, Comm)))*0.02 AS RevisedPackage
FROM Emp
GROUP BY EName)
SELECT
COUNT(*) TotalEmployees,
SUM(CurrentPackage) CurrentPackage,
SUM(RevisedPackage) RevisedPackage
FROM CTE_Emp
Hope you find this article helpful.
Happy learning!!!
Big Data and SQL 
2 comments