SQL Extensions

CTE

A non-recursive CTE names a compiled subquery result for the body.

highlighted = computed this step

CTE

A CTE binds a named compiled subquery result for the body. This surface is non-recursive and makes no materialization claim.

named compiled child\text{named compiled child}

Named subquery output

The query has 1 CTE output and 3 final rows, with 1 unmatched course row.

CTEs=1,rows=3\text{CTEs}=1,\quad \text{rows}=3

SQL extensions are rendered as tiny finite-table semantics; SQL dialect completeness, optimizer behavior, execution cost, full DDL behavior, and database-product claims are out of scope.

compiled summaryfactvaluerowCount3arity3rootKindwith CTECourses.courseCourseAvg.courseCourseAvg.avg_scoreAINULLNULLDBDB80/1OSOS80/1 CTE outputsnamerowCountarityCourseAvg32

Summary

The CTE is a fail-closed wrapper around named compiled results. Note: recursion and materialization are explicitly out of scope.

non recursive CTE only\text{non recursive CTE only}