loading..
Русский    English
12:08

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
  1. SELECT ship FROM Outcomes
  2. EXCEPT
  3. 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
  1. SELECT ship FROM Outcomes
  2. EXCEPT
  3. 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:

  1. SELECT ship FROM Outcomes
  2. EXCEPT ALL
  3. SELECT name FROM Ships;
(2 duplicates for 'California' in Outcomes table minus 1 - in Ships)

Accordingly the query

  1. SELECT ship FROM Outcomes
  2. EXCEPT ALL
  3. 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

Bookmark and Share
Pages 1 2 3 4
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
продать тинькофф
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100