Intersect and Except page 2
Find the ships from Outcomes table which are absent from Ships table
The problem is easily solved by means of EXCEPT statement:
Operation of subtraction is not commutative, therefore if we change the order of the queries, then we will obtain the solution of another problem:
"Find ships in the Ships table, which are absent in Outcomes table."This problem in terms of subject matter can be formulated in the following way: "Select the ships, which did not participate in the battles."
Pls. note, that if some ship has taken part in several battles, then it will be selected only once for the resulting set for the reason of excluding of the duplicates. We have got such a ship - California, but it is included into Ships table also, and that is why it is not selected by the aforementioned query. That is why in order to illustrate what has been said above, let’s exclude it from the result of the second query of the subtraction operation:
For the available data set we will obtain the same result while executing the following query:
Accordingly the query