Exercise #56 page 2
Here is one more solution to this task, which does not use UNION, but contains another mistake:
The first (full) join
Then the left join with sunken ships from Outcomes table is taken on the following predicate (the set s includes all the sunken ships):
Thus, result set includes a ship if her name coincides with a name of sunken ship or if the class coincides with a name of sunken ship. For the examples of data that we considered before, the above query will work correctly, unlike of solution 3.12.1. Indeed, if two sunken ships belong to the Bismarck class and one of these is a head ship absent from Shipa table, the both will be accounted up due to above predicate. On the other hand, if the head ship is presented in Ships table, it does not matter as the predicate will be evaluated as true also.
But where is mistake in the solution? The mistake consists just in the predicate of latter join. Let Ships table include unsunken ships of any class (for example, ships "A" and "B" of Class_1 class). In addition, let Outcomes table have sunken head ship of the same class. Then the following tables will be joined (only columns meaningful for analysis are retained):
As a result the table will be obtained that includes ships which did not be sunken but be considered by this solution:
In other words, sunken head ship is counted up not once but with each ship of the same class in the Ships table (both the sunken and unsunken). Anyhow, but COUNT (ship) = 2, that is incorrect, as only one ship has been sunken.
By the way, it is obviously from what have been said how to correct this solution; this is very simply in so doing. You can add 8 characters. :-) Do you ready