Database Related

Difference Between A Subquery And A Derived Table

Both subqueries and derived tables can look same and most of the people think they are the same thing, however there are some major differences.

Subquery

A subquery is a SELECT statement which is nested within another statement. This is why it’s known as a subquery, because it’s similar to have a query within another query. Usually, subqueries are used in the WHERE clause as a way for filtering out specific rows returned in the result set of the outer query.

For example, we have a table called labor with columns labor_name, last_name, labor_salary, and labor_number. And we also have another table called branch that has columns called manager_labor_number and branch_name.

Using all these tables as our sample data, here is what a subquery looks like:

Select labor_name

from labor

where labor_salary>

— this is a subquery:

(select avg(labor_salary)

from labor)

 

The SQL above will find all labors that have above average salary.

Derived Tables

Basically, a derived table is a subquery, except it is in the FROM clause of a SQL statement always. It is known as a derived table because it fundamentally functions as a table as far as the whole query is concerned.

However, remember that a derived table just exists in the query in which it is made. Therefore, derived tables are not essentially part of the database schema as they are not real tables.

The below example will help in clarifying a derived table:

select max (qualification)

from (

— this portion of the query is a derived table:

select qualification from table

) as qualification — must give derived table an alias

In the above SQL, you can see that the derived table is in the FROM part of the SQL. The results of the “Select qualification from table” query are considered to be the derived table. Besides, it is quite obvious to you how the derived table basically acts as a table from which something else is chosen.

It is necessary to include the “as qualification” text on the bottom of the above SQL; otherwise you will get an error saying “Every derived TABLE should have its own alias”.

Leave a Reply