Common mistakes in solving SELECT statement exercises

Database «Ships»

Solution 3.2.4

Approach based on unions is not unique. The following solution uses joins.

Console

Execute

SELECT Classes.class
FROM Outcomes RIGHT OUTER JOIN
 Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN
 Ships ON Classes.class = Ships.class
GROUP BY Classes.class
HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

SELECT Classes.class FROM Outcomes RIGHT OUTER JOIN Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN Ships ON Classes.class = Ships.class GROUP BY Classes.class HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

    Scheme

Right join of Outcomes and Classes tables gives us the leading ships, with so doing the Outcomes.ship column will contain NULL value if the leading ship will not be in the Outcomes table. Then is executed left join of Classes and Ships tables on foreign key. The Ships.name column will contain a NULL value if the class has no ships in the Ships table. The resulting record set is grouped by class, and then the filtering predicate is used

COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1

Let us stop on this elegant method.

So to make things completely clear, we give examples of four possible rows (taken from the available database except the last case), which result from the join. Here they are:

NULL in the column name for the Bismarck class means that the lead ship is only available in the Outcomes table. Ships "California" and "Tennessee" of Tennessee class are in Ships table, in this case, the leading ship is also in the Outcomes table. In the third case, the two ships of class Yamato present in the Ships table, the Outcomes table does not have leading ship of the class. For the fourth case of Class_1, the class has no ships in the database.

We return to the predicate. Function COALESCE (Outcomes.ship, Ships.name) returns the first non-NULL value of its arguments, or NULL, if both arguments are NULL values. More information about the COALESCE function can be found in paragraph 5.10.

The COUNT aggregate function with an argument counts non-NULL values in a group. The result for the Bismark class is 1; the result for the Tennessee and Yamato classes is 2 and for the Class_1 is 0. Only the Bismark of the four classes is present in the resulting set because the predicate is true for this class only.

This solution is free from the mistake of the previous solution 3.2.3: a leading ship contained both in the Ships table and the Outcomes table will be counted just once, because the only row will be present in the resulting set.

Furthermore the same error as in the example 3.2.1  rises in this case. If the row mentioned in the example is inserted into the database, just one row will be produced for two Bismark class ships as a result of the union:

If you have not yet discovered the mistake mentioned in the inset, look up the T&S.

T&S