Common Table Expressions

The CTE Output Table

The CTE query becomes a visible table.

highlighted = computed this step

The CTE output table

The CTE body is compiled into a concrete output table.

CTE has rows and columns\text{CTE has rows and columns}

Read the output size

The CTE output has 2 rows and 2 columns.

CTE rows=2\text{CTE rows}=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.

The CTE output table: base Studentssidnamestatus1Adaactive2Beapaused3Calactive The CTE output table: CTE ActiveStudentssidstudent1Ada3Cal The CTE output table: body outputsidstudent1Ada3Cal CTE factsfactvaluebaseTableCount1cteCount1cteNames[ActiveStudents]bodyRowCount2bodyColumnCount2referencedCtes[ActiveStudents]rowOrdersource_orderrecursivenomaterializedClaimnot_modeled

Filtered and projected

Only active students are kept, and the output columns are sid and student.

filter then project\text{filter then project}

Summary

The name stores the compiled result table for the body query to use.

named result table\text{named result table}