A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known.
Example 1 shows an attempt to use a column alias where another clause is processed before the alias name is known.
EXAMPLE 1 (EXAMPLE OF AN ILLEGAL STATEMENT)
Get all existing groups of months from the enter_date column of the works_on table:
The result is:
The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name enter_month is not known at the time the grouping is processed.
By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause, as shown in Example 2.
EXAMPLE 2
The result is:
Generally, it is possible to write a table expression any place in a SELECT statement where a table can appear. (The result of a table expression is always a table or, in a special case, an expression.) Example 3 shows the use of a table expression in a SELECT list.
EXAMPLE 3
The result is:
Leave a Reply