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 *
(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 *

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.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.