Русский    English

Exercise #37 page 4

Solution 3.2.4

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

  1. SELECT Classes.class
  3. Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN
  4. Ships ON Classes.class = Ships.class
  5. GROUP BY Classes.class
  6. HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

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

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

Let us stop on this elegant method.


The author is not ironic when he says "beautiful", "elegant", etc., on the wrong approach to solving the exercise. Here, most of the queries are written by professionals, fluent in the language of SQL. The analysis such solutions is a good school for beginners. The mistakes in these solutions are usually associated with neglect of a particular subject area, and are often easily corrected by purely cosmetic means.

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:

Ship class name
Bismarck Bismarck NULL
Tennessee Tennessee California
Tennessee Tennessee Tennessee
NULL Yamato Musashi
NULL Yamato Yamato

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.


A question. Is the last assertion always true for our database? Provide an example of data when it is not true. Incidentally this adds another error to the solution.

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:

Ship Class name
Bismarck Bismarck Tirpitz

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


To solve the problem on SQL-EX.RU

Bookmark and Share
Pages 1 2 3 4
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.