WITH QueryName [(ColumnName, ...)] AS (
SELECT ...
)
SELECT ... FROM QueryName ...;
WITH RECURSIVE QueryName [(ColumnName, ...)] AS (
SELECT ...
UNION [ALL]
SELECT ... FROM QueryName ...
)
SELECT ... FROM QueryName ...;
Official documentation: WITH clause
A Common Table Expression is a temporary result set, and it can be result of complex sub query. It is defined by using WITH clause. CTE improves readability and it is created in memory rather than TempDB database where Temp Table and Table variable is created.
Key concepts of Common Table Expressions: