SQL-Derived Tables Usage

You can use SQL-derived tables to form part of a larger integrated query that uses assorted SQL clauses and operators.

• Nesting
  A query can use numerous nested derived-table expressions, which are SQL expressions that define a SQL-derived table.
• Subqueries Using SQL-Derived Tables
  You can use a SQL-derived table in a subquery from clause.
• Unions in Derived-Table Expressions
  A union clause is allowed within a derived-table expression.
• Unions in Subqueries
  A union clause is allowed in a subquery inside a derived-table expression.
• Rename Columns with SQL-Derived Tables
  If a derived column list is included for a SQL-derived table, it follows the name of the SQL-derived table and is enclosed in parentheses.
• Constant Expressions
  If a column name is not specified in the target list of the derived-table expression, as in the case where a constant expression is used for the column name, the resulting column in the SQL-derived table has no name.
• Aggregate Functions
  Derived-table expressions can use aggregate functions, such as sum, avg, max, min, count_big, and count.
• Joins with SQL-Derived Tables
  You can use the where clause to join a SQL-derived table and an existing table.
• Create a Table From a SQL-Derived Table
  Data obtained from a SQL-derived table can be inserted into a new table.
• Views with SQL-Derived Tables
  You can create a view using a SQL-derived table.
• Correlated Attributes
  You cannot reference correlated attributes that exceed the scope of a SQL-derived table from a SQL-derived-table expression.