SQL Derived Table Vs Sub-Query The Top Differences

Derived Table in DB2 you can call as a temporary table. When the main query ends, the derived table also ends. With SELECT statement, you can create a derived table.

Key Interview Points on Derived Table

  • Exists only within a query
  • Materialized by a SELECT Statement inside a query
  • Space comes from the user’s space
  • Deleted when the query ends

SQL Query

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.

derived table in DB2 works within the query.

SQL Query for Derived Table in Other way

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

The name with clause is “sriniderived” you can call it as derived table.

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.