SQL Common Table Expressions
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).
- Can be a substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Will be run when called, not when defined. If the CTE is used multiple times in a query it will be run multiple times (possibly with different results).
