Hack 6. Dont Perform the Same Calculation Over and Over
Hack 6 Don t Perform the Same Calculation Over and Over
The FROM clause of a SELECT statement may include other SELECT statements. This feature can simplify a complex statement.
Sometimes using a derived table statement is the only way to get the results that you want. But you can also use a derived table to make a query shorter and easier to read. When you have the same complicated expression cropping up in several places in your output you can use a derived table to provide a kind of local variable.
The contract table contains two columns: income and overhead. You want to produce five more columns calculated from these two values. The output would look like Table 1-2.
| Income | Overhead | Residual:grant minus overheads | Est:20% of residual | Admin:10% of residual | Rsrv:5% of residual |
|---|---|---|---|---|---|
| $1,000 | 20% | 800 | 160 | 80 | 40 |
| $2,000 | 10% | 1,800 | 360 | 180 | 90 |
| $1,000 | 50% | 500 | 100 | 50 | 25 |
The SQL to generate this table is not complicated, but it is rather lengthy:
mysql> SELECT income, -> overhead, -> (income-income*overhead/100) AS residual, -> 0.20*(income-income*overhead/100) AS Est, -> 0.10*(income-income*overhead/100) AS Admin, -> 0.05*(income-income*overhead/100) AS Rsrv -> FROM contract; +--------+----------+----------+------+-------+------+ | income | overhead | residual | Est | Admin | Rsrv | +--------+----------+----------+------+-------+------+ | 1000 | 20 | 800 | 160 | 80 | 40 | | 2000 | 10 | 1800 | 360 | 180 | 90 | | 1000 | 20 | 500 | 100 | 50 | 25 | +--------+----------+----------+------+-------+------+
It would be neater if you didn't have to keep repeating that residual calculation (income-income*overhead/100) over and over again.
You can calculate the residual in a derived table and then refer to it in the outer query. With indentation and a consistent method for naming columns, a derived table can improve the appearance of the SQL:
mysql> SELECT income, -> overhead, -> residual, -> 0.20*residual AS Est, -> 0.10*residual AS Admin, -> 0.05*residual AS Rsrv -> FROM -> (SELECT income, overhead, (income-income*overhead/100) AS residual -> FROM contract) subquery; +--------+----------+----------+------+-------+------+ | income | overhead | residual | Est | Admin | Rsrv | +--------+----------+----------+------+-------+------+ | 1000 | 20 | 800 | 160 | 80 | 40 | | 2000 | 10 | 1800 | 360 | 180 | 90 | | 1000 | 20 | 500 | 100 | 50 | 25 | +--------+----------+----------+------+-------+------+
In this case, the query with the subquery is not shorter than the original, but it is easier to understand, and if the residual calculation changes it will be easier to update the query.
This technique can turn a completely unreadable query into a relatively compact, maintainable format.
1.6.1. Use a VIEW
Another alternative is to turn the derived table into a VIEW:
CREATE VIEW residual1 AS SELECT income, overhead, (income-income*overhead/100) AS residual FROM contract; SELECT income, overhead, residual, 0.20*residual AS Est, 0.10*residual AS Admin, 0.05*residual AS Rsrv FROM residual1;
Without the right precautions, this approach can lead to difficulty in managing the collection of views that clutter your workspace. When you have a chain of views that lead to a final result you should name them so that they are listed together with the main result. You could call the final query residual and ensure that the queries that residual depend on are called residual1, residual2, and so on.
1.6.2. Hacking the Hack
In some cases, the base table has many columns that need to appear in the outer query. The following example contains only two columns: income and overhead. But if it contained five or ten columns, having to list every column name in the derived table would cause more hassle than the hack eliminates.
SQL allows you to use the * wildcard to include all of the columns from a specified table. You can use it in the subquery and in the outer query. If your goal is more readable SQL, you should use it sparingly:
mysql> SELECT subquery.*, -> 0.20*residual AS Est, -> 0.10*residual AS Admin, -> 0.05*residual AS Rsrv -> FROM -> (SELECT contract.*, (income-income*overhead/100) AS residual -> FROM contract) subquery; +--------+----------+----------+------+-------+------+ | income | overhead | residual | Est | Admin | Rsrv | +--------+----------+----------+------+-------+------+ | 1000 | 20 | 800 | 160 | 80 | 40 | | 2000 | 10 | 1800 | 360 | 180 | 90 | | 1000 | 40 | 500 | 100 | 50 | 25 | +--------+----------+----------+------+-------+------+
|