Learn T-SQL Querying

Using Common Table Expressions

Common Table Expressions (CTEs) are runtime constructs to derive an inline intermediate result set from a query. This means that a complex T-SQL query can be broken down into simpler T-SQL statements that store intermediate results before joining with other tables or other CTEs that had been previously defined in the T-SQL statement. For example, take the two following queries that can be executed in the AdventureWorks sample database:

WITH Sales_CTE(SalesPersonID, SalesOrderID, SalesYear)AS(  SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  FROM Sales.SalesOrderHeader  WHERE SalesPersonID IS NOT NULL)SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYearFROM Sales_CTEGROUP BY SalesYear, ...