O'Reilly logo

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL by Joe Celko

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required