SQL Extensions

Derived Table

A derived table is a compiled subquery result used as a child table.

highlighted = computed this step

Derived table

A derived table compiles a subquery result and renames it as a child table. The rows remain finite bag rows.

subquery as table\text{subquery as table}

Renamed child rows

The derived child contains 5 rows. Grouping that child produces 3 output groups.

child rows=5,groups=3\text{child rows}=5,\quad \text{groups}=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 summaryfactvaluerowCount3arity2rootKindgroup_avg Derived tablecourseavg_scoreDB80/1M10/1OS80/1 AVG non-NULL countsgroupnonNullCount[DB]2[M]1[OS]1

Summary

The derived table is just a compiled child result in this model. Note: correlated subqueries and planner choices are out of scope.

compiled child then parent\text{compiled child then parent}