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 avg(salary) as avg_salary from employee_table)
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.