00:01

Intersect and Except page 2

Example 5.7.4

Find the ships from Outcomes table which are absent from Ships table

The problem is easily solved by means of EXCEPT statement:

Console
Execute
`SELECT ship FROM OutcomesEXCEPTSELECT name FROM Ships;`

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:

Console
Execute
`SELECT ship FROM OutcomesEXCEPTSELECT name FROM Ships WHERE name <> 'California';`

ship
Bismarck
California
Duke of York
Fuso
Hood
King George V
Prince of Wales
Rodney
Schamhorst
West Virginia
Yamashiro

For the available data set we will obtain the same result while executing the following query:

`SELECT ship FROM OutcomesEXCEPT ALLSELECT name FROM Ships;`
(2 duplicates for 'California' in Outcomes table minus 1 - in Ships)

Accordingly the query

`SELECT ship FROM OutcomesEXCEPT ALLSELECT name FROM Ships WHERE name<>'California';`
will generate double selection of California ship in the resulting data set (2 - 0 = 2):

ship
Bismarck
California
California
Duke of York
Fuso
Hood
King George V
Prince of Wales
Rodney
Schamhorst
West Virginia
Yamashiro

Suggested exercises: 8, 80

 Pages 1 2 3 4
Tags
The book was updated
several days ago
продать тинькофф