7.1. Derived Tables
A derived table is a table expression embedded in a containing statement. It has to be placed inside parentheses. It can optionally be given a correlation name, and its columns can also optionally be given names.
(<table expression>) [[AS] <correlation name> [(<derived column list>)]]
The derived table will act as if it is materialized during the duration of the statement that uses it. Notice the phrase “act as if in that last sentence. The optimizer is free to rearrange the statement in any way that it wishes, so long as the results are the same as the original statement.
Materialization is not an easy choice. If one statement is using a derived table, it might be better to integrate it into that statement. But if many statements ...
Get Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
