Derived Table Vs. Sub-Query – Here are Top Differences

Here are the differences between the derived table and the sub-query. You can create derived table with the SELECT statement. But the sub-query is different. You can see here why.

Quick Notes on Derived Table

Exists only within a query. That means it’s a temporary table.
Materialized by a SELECT Statement inside a query
Space comes from the user’s space
The table gets deleted when the query ends

Here’s SQL Query for Derived table

select *
from 
(select avg(salary) as avg_salary from employee_table) 
as sriniderived;

Here, sriniderived is the derived Table. The avg_salary is the column name. The query inside the ‘()’ is called SELECT statement for derived table.

Other Way to Write SQL Query

with sriniderived(avgsal) as
(select avg(salary) from employee_table)
select *
from
sriniderived;

The SQL query with select statement in “()” is for dervied table. That means derived table will form here. Next you need to give SELECT * statement to retrieve the data.