01:45

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Conventional set-theoretic operations and the SELECT statement

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 Outcomes
EXCEPT
SELECT 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 Outcomes
EXCEPT
SELECT 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 Outcomes
EXCEPT ALL
SELECT name FROM Ships;

(2 duplicates for 'California' in Outcomes table minus 1 - in Ships)

Accordingly the query

SELECT ship FROM Outcomes
EXCEPT ALL
SELECT 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

INTERSECT EXCEPT ALL

Previous [Union]

[Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators] Next

Last added:

Tags

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
several days ago

продать тинькофф

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Conventional set-theoretic operations and the SELECT statement

Intersect and Except page 2