Some complex queries involving more than one layer of calculations cannot be easily done in SQL. Let’s say you need to compute the average number of sales per customer. Your first thoughts may be something in the lines of :
SELECT AVG(COUNT(SALE_ID)) FROM SALES GROUP BY CUSTOMER_ID
But when you try to execute this kind of query, you will get an error message looking like this :
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
At this point, your instinct should tell you that the average of sales per customer should be something relatively easy to compute using SQL. Some folks use temporary tables as a workaround to this limitation. This solution is very bad for your query performance, especially when you have to deal with large databases. The flow for such a task is :
1– Create a new temporary table to hold the computed values.
2– Insert the values in the new table.
3– Select the value you need from the temporary table.
4– Delete the temporary table.
The easier way that’s a lot faster both to write and to execute is to use derived tables.
SELECT AVG(number_of_sales) FROM (SELECT COUNT(SALE_ID) AS number_of_sales FROM SALES GROUP BY CUSTOMER_ID) dt_sales
The magic here lies in the FROM part. The FROM keyword can use tables OR derived tables. A derived table is simply created by writing the query and giving it an alias (in my examplen it is dt_sales). Derived tables are created only in memory and thus are a lot faster than on-disk temporary tables.
Derived tables can be used exactly as regular database tables. For example, you can make joins between them or between regular tables and derived tables.