When and How to Apply SQL Server Derived Tables

By: Dusan Petkovic


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:

0233_001

 

The result is:

 

0233_002

 

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

0233_003

 

The result is:

198_01

 

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

0234_001

 

The result is:

198_02

 

 

Leave a Reply