Derived Tables

Friends,

As we all know that there are TWO types of Table Expressions and the same is provided below. Table Expressions are nothing but subqueries that are used where a table is expected.

• Derived Tables
• CTEs

In this post we are gonna discuss about “Derived Tables”. 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.

Lets take a simple example to demonstrate the same. Imagine I have a table with the following columns.

• Date – Which stores the date of sales
• Sales – Which stores the sales amount.

Now my requirement is to see the sales MONTH wise and for achieving the same we need to use Group By clause as shown below.

select Month(Date) as Month_Name,Sum(Sales) as Sales from Table1
Group By Month_Name

If I execute the above query, it throws the following error.

Message 207: Level 16, State 1, Line 2
The invalid column ‘Month_Name‘

The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name of the column Month_Name 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 and you can see the modified query given below.

SELECT Month_Name,Sales
FROM (SELECT MONTH(Date) as enter_month,Sum(Sales) as Sales
FROM works_on) A
Group By Month_Name

In the above query, the processing of column MONTH(Date) is executed before it triggers GROUP BY clause and this is why the data will be grouped by MONTH and return the results.

Also remember that DERIVED Table can be added in any part of SELECT statement like in Columns list,Where or FROM. You can use these as REPLACEMENT of views also.

Regards,
Roopesh Babu V