Syntax

• WITH QueryName [(ColumnName, …)] AS (

SELECT …

)

SELECT … FROM QueryName …;

• WITH RECURSIVE QueryName [(ColumnName, …)] AS (

SELECT …

UNION [ALL]

SELECT … FROM QueryName …

)

SELECT … FROM QueryName …;

Remarks

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:

• Can be used to break up complex queries, especially complex joins and sub-queries.
• Is a way of encapsulating a query definition.
• Persist only until the next query is run.
• Correct use can lead to improvements in both code quality/maintainability and speed.
• Can be used to reference the resulting table multiple times in the same statement (eliminate duplication in SQL).