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.

Table 1-2. Sharing the residual

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 |
+--------+----------+----------+------+-------+------+

Beware that using SELECT * can hamper performance because you will be selecting all of the columns, including any large text and binary data stored in the table.