Common Table Expression (CTE) – Common Table Expressions

A CTE can be understood as a temporary result set defined within the scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE A CTE is a named expression defined in a query. The CTE is defined at the start of the query and can be referenced multiple times in the outer query.

A CTE that includes references to itself is called a recursive CTE.

The strength of CTE is to develop the ability to read commands and easily maintain a complex command.

Syntax:

WITH <CTE_Name> AS (<CTE_definition>)

For example, to get and display the annual customer for the orders contained in the Sales.SalesOrderHeader table:

WITH CTE_OrderYear AS (SELECT YEAR(OrderDAte) AS OrderYear,CustomerID FROM Sales.SalesOrderHeader) SELECT OrderYear, COUNT(DISTINCT CustomerID) AS CustomerCount FROM CTE OrderYear GROUP BY OrderYear;

In the above example, CTE_OrderYear is specified as the name of the CTE. The WITH … AS keyword begins to define a CTE, then the CTE is used in the SELECT statement to retrieve and display the entire result.

Multiple CTEs can be defined together in the WITH clause. For example:

WITH CTE_Students AS ( SELECT S.StudentCode, S.Name, C.CityName, St.Status FROM Student S) INNER JOIN City C ON S.CityCode = C.CityCode INNER JOIN Status St ON S.StatusId = St.StatusID), StatusRecord AS ( SELECT Status, COUNT(Name) AS CountofStudents FROM CTE_Students GROUP BY Status ) SELECT * FROM StatusRecord