Common Table Expressions

Join the Body with the CTE

The body can join a base table with the CTE result.

highlighted = computed this step

Join the body with the CTE

The body can join a base table to the named CTE result.

base table join CTE\text{base table join CTE}

Read the joined output

The body references ActiveStudents and produces 3 joined rows.

joined rows=3\text{joined rows}=3

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

Join the body with the CTE: base Studentssidnamestatus1Adaactive2Beapaused3Calactive Join the body with the CTE: base Scoressidscore1A1A23C4D Join the body with the CTE: CTE ActiveStudentssidstudent1Ada3Cal Join the body with the CTE: body outputleft.sidleft.scoreright.sidright.student1A1Ada1A21Ada3C3Cal CTE factsfactvaluebaseTableCount2cteCount1cteNames[ActiveStudents]bodyRowCount3bodyColumnCount4referencedCtes[ActiveStudents]rowOrdersource_orderrecursivenomaterializedClaimnot_modeled

Source order

Rows follow the finite source order: score rows first, then matching CTE rows.

source order\text{source order}

Summary

The CTE makes the body query easier to read, but the output is still recomputed.

recomputed body\text{recomputed body}