Exercise #32 page 3
The mistake lies in the join.
It it visible only in case when some ship has participated in multiple battles, as then it will be counted multiple times in a result set. But we do not claim that we can’t use a full outer join here. However, you need to care about excluding the duplicates.
Let’s consider a case when there is class (e.g. name – class_1, gun caliber – 12) without ships of this class in the database and another class (e.g. name – class_2, gun caliber – 14) which has only one main ship in the database referred to in the Outcomes table. In this case both classes belongs to one country, let’s say, country_1. For more clarity, add a class column to the table, then the first query within union will produce the following results:
As you can see, we will consider two odd rows in our result set.