Common Table Expressions

Base Tables

A CTE starts from visible finite tables.

highlighted = computed this step

Start with base tables

A CTE starts from ordinary finite tables.

base tables first\text{base tables first}

Read the base tables

The compiler sees 2 base tables before it trusts any CTE output.

base tables=2\text{base tables}=2

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

Base tables: base Studentssidnamestatus1Adaactive2Beapaused3Calactive Base tables: base Scoressidscore1A1A23C4D Base tables: CTE ActiveStudentssidstudent1Ada3Cal Base tables: body outputleft.sidleft.scoreright.sidright.student1A1Ada1A21Ada3C3Cal CTE factsfactvaluebaseTableCount2cteCount1cteNames[ActiveStudents]bodyRowCount3bodyColumnCount4referencedCtes[ActiveStudents]rowOrdersource_orderrecursivenomaterializedClaimnot_modeled

Keep inputs visible

The visual shows the input rows beside the named CTE result and the body result.

inputs visible\text{inputs visible}

Summary

A CTE is not magic; it is built from visible rows.

visible source rows\text{visible source rows}