Common Table Expressions

Define One CTE

A non-recursive CTE names one compiled result table.

highlighted = computed this step

Define one CTE

A non-recursive CTE gives a name to one compiled table result.

WITH ActiveStudents AS (...)\text{WITH ActiveStudents AS (...)}

Read the name

The request defines 1 CTE named ActiveStudents.

CTE count=1\text{CTE count}=1

CTE examples are tiny finite-table transforms; recursive CTEs, materialization claims, optimizer behavior, SQL dialect completeness, and database-product claims are out of scope.

Define one CTE: base Studentssidnamestatus1Adaactive2Beapaused3Calactive Define one CTE: CTE ActiveStudentssidstudent1Ada3Cal Define one CTE: body outputsidstudent1Ada3Cal CTE factsfactvaluebaseTableCount1cteCount1cteNames[ActiveStudents]bodyRowCount2bodyColumnCount2referencedCtes[ActiveStudents]rowOrdersource_orderrecursivenomaterializedClaimnot_modeled

One name, one table

The name points to the recomputed CTE output table.

name points to rows\text{name points to rows}

Summary

This book only models small non-recursive CTEs.

non recursive only\text{non recursive only}