/* A Common Table Expression (CTE) in T-SQL acts as a temporary result set that you can reference within a subsequent SELECT, INSERT, UPDATE, or DELETE statement. It is excellent for breaking down complex queries, replacing subqueries, and improving code readability. */ USE MyDB; GO /* Simple CTE Example (Replacing a Subquery) This example calculates the total basic salary by department. The CTE acts as a virtual table named DeptTotalSalary that groups the basic salary the main query aggregates it. */ WITH DeptTotalSalary AS ( SELECT [Department] AS [DID], SUM([Basic Salary]) AS [Total Department Salary] FROM [Employee] GROUP BY [Department] ) -- Main query referencing the CTE SELECT e.[EID],e.[Name], e.[Basic Salary], d.[Total Department Salary], CAST(100.0*e.[Basic Salary]/d.[Total Department Salary] AS DECIMAL(16,2)) [%] FROM [Employee] e JOIN DeptTotalSalary d ON e.[Department] = d.[DID]; /* Multiple CTEs Example You can chain multiple CTEs together by separating them with a comma. Each CTE can reference any previously defined CTE above it. */ --The following try to find out the employees who play both supervisor and head roles WITH Supervisor AS ( SELECT [EID], [Name] FROM [Employee] e WHERE EXISTS( SELECT * FROM [Employee] WHERE [Supervisor]=e.[EID]) ), Head AS ( SELECT [Department Name] AS [Department], [HID] FROM [Department] ) -- Main query joining to a physical table SELECT s.[Name] FROM Supervisor s INNER JOIN Head h ON s.[EID] = h.[HID] /* Recursive CTE (Hierarchy) A recursive CTE references itself to parse hierarchical data structures, like an organizational chart. It requires an Anchor Member (the starting point) and a Recursive Member combined by a UNION ALL */ WITH EmployeeHierarchy AS ( -- 1. Anchor Member: Top-level supervisor (who don't have a supervisor) SELECT [EID], [Name], [Supervisor], 1 AS OrgLevel FROM [Employee] WHERE [Supervisor] IS NULL UNION ALL -- 2. Recursive Member: Join the table back to the CTE to find subordinates SELECT e.[EID], e.[Name], e.[Supervisor], h.OrgLevel + 1 AS OrgLevel FROM [Employee] e INNER JOIN EmployeeHierarchy h ON e.[Supervisor] = h.[EID] ) -- Main query displaying the full tree structure SELECT * FROM EmployeeHierarchy ORDER BY OrgLevel, [Name]; /* Core Syntax Rules to Remember • Semicolon Prefix: It is a best practice to start your WITH clause with a semicolon (;WITH...) if there are statements preceding it, as T-SQL requires the statement before a CTE to be properly terminated. • Single Scope: A CTE can only be referenced by the immediate next statement. You cannot reference it later on in the batch. Useful References: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver17 https://learnsql.com/blog/cte-in-t-sql/ */