Derived Tables

A “derived table” is essentially a statement-local temporary table created by means of a subquery in the FROM clause of a SQL SELECT statement. It exists only in memory and behaves like a standard view or table.

For example, assume an Oracle 9i database table is called “state_table” and has the following row values in the “state” column.

If you used the following inner SELECT statement, which includes a derived table to evaluate the “state_table”, you could return the count and percentage of each state. The SQL has been written for Oracle 9i.

select 		state, count(state) as State_Count,
	(count(state)/derived_table.tot_state_count) as State_Percentage
from State_table, 
	(select count(state) tot_state_count from state) derived_table
group by state, derived_table.tot_state_count;

The results of the query is displayed below:

Derived tables are useful when you need to generate aggregates in a table that would otherwise contain dimension type data and join the resulting aggregate with detail level facts in another table.

Additionally, the aggregate values in the derived tables can be used in the outer query's WHERE clause (i.e., "where salary > average_salary", average_salary has been derived by the subquery). These tables can also optimize a query's performance in some circumstances, such as minimizing sorting when some DISTINCT values are needed from some tables but not all. Finally, it might eliminate the need in some cases, to build "local results" queries. This feature enables users to access this type of SQL construct in an easy-to-build manner.