What is derived table in SQL server?

Question

Can anyone explain the derived table in the SQL server?

Answer 1

Derived table is a table expression used in the FROM clause of the query. Derived table is used when the use of column aliases is not possible since another clause like group by is processed by the SQL translator before the alias name is known.

If you run the following query, you will get an error stating invalid column ‘enter month’. This is because the alias name enter_month is not known at the time group by clause is processed.

SELECT Month (enter_date) as enter_month

FROM works_on

GROUP BY enter_month;

That’s why we use a derived table that is used in the FROM clause since the FROM clause is executed before the GROUP BY clause and here is the query for that:

SELECT enter_month

FROM (SELECT MONTH (enter_date) as enter_month

                FROM works_on) AS m

GROUP BY enter_month;

In order to learn SQL from top experts, you can enroll in this SQL training course by Intellipaat that also provides certification.