Page 38 - SQL

Chapter 8: 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).

Examples

Temporary query

These behave in the same manner as nested subqueries but with a different syntax.

WITH ReadyCars AS (
SELECT *

https://riptutorial.com/ 20