Common table expressions (CTEs) are another kind of table expression that like derived tables are visible only to the statement that defines them. There are no session-scoped or batch-scoped CTEs. CTEs have a CTE name, an inner query and an outer query. CTEs ae easier to work with that derived tables. The code start with naming the CTE, the inner query expressed from start to end uninterrupted and then the outer query also expressed from start to end uninterrupted.

Here is a SQL Server database diagram from the database referenced in the Microsoft Press book T-SQL Querying by Ikzik Ben-Gan et al published in 2015. We will use this as an example when we write our CTEs. A customer can place many orders and each order can have many products. Our query only uses the Orders table.

Here is an example of CTE code in T-SQL that is used in the above-mentioned book on page 207. It starts with the WITH command.

WITH OrdCount AS (
  SELECT 
     YEAR(orderdate) AS orderyear,
     COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT orderyear, numorders
FROM OrdCount;

Here is the result of that query in SSMS.