The query expression for a SQL-derived table is specified in the from clause of the select or select into command.
from_clause ::= from table_reference [,table_reference]...
table_reference ::= table_view_name | ANSI_join
table_view_name ::=
{table_view_reference | derived_table_reference}
[holdlock | noholdlock]
[readpast]
[shared]
table_view_reference ::=
[[database.]owner.] {table_name | view_name}
[[as] correlation_name]
[index {index_name | table_name }]
[parallel [degree_of_parallelism]]
[prefetch size ]
[lru | mru]
derived_table_reference ::= derived_table [as] correlation_name [’(’ derived_column_list’)’]
derived_column_list ::= column_name [’,’ column_name] ...
derived_table ::= ’(’ select ’)’
A derived-table expression is similar to the select in a create view statement and follows the same rules, except:
Temporary tables are permitted in a derived-table expression except when it is part of a create view statement.
A local variable is permitted in a derived-table expression except when it is part of a create view statement. You cannot assign a value to a variable within a derived-table expression.
You cannot use variables in derived table syntax as part of a create view statement where the derived table is referenced in cursors.
A correlation_name, which must follow the derived-table expression to specify the name of the SQL-derived table, may omit a derived column list, whereas a view cannot have unnamed columns:
select * from (select sum(advance) from total_sales) dt