Sub queries vs Derived Tables in SQL

Sub queries are best defined as query within a query. An example of a sub query is as follows:

select employee_name

from employee

where employee_salary >

-- this is a subquery:

(select avg(employee_salary)

        from employee)

Here, the subquery is within the where clause. It could be put in the in clause as well as:

Select * from employee where employee in(Select employee from employee_details where salary > avg(salary)

Now consider the following set of queries:

Create view view1 as Select salary from employee (this is for the query in the from clause)

Select max(salary) from view1

Drop view1

This set of queries using view involves writing values to the system catalog tables and three statements have to be written. Instead we could use a derived table.

Derived table is a table which is temporary , and created when you write a query within the From Clause as in:

Select max(salary) from (select salary from employee) As Max_salary

The point to be noted here is that the use of As clause is a must , without which we would get the error : “Every derived TABLE must have its own alias”.

Benefit of Derived table over using views:

i)                    No entry is made to the system catalog table.

ii)                   The temporary table made for the query within the from clause exists until the query is executed i.e the duration of the query

iii)                 It is faster than queries with views.