Combining with EXCEPT






Combining with EXCEPT

The third set operator is the EXCEPT operator. If two table expressions are combined with the EXCEPT operator, the end result consists of only the rows that appear in the result of the first table expression but do not appear in the result of the second. EXCEPT is the equivalent of the difference operator from set theory. Just as with the UNION operator, duplicate rows are automatically removed from the result.

Portability

Several SQL products, including MySQL, do not support the EXCEPT operator.


Figure. Get the player number and the date of birth of each player who lives in Stratford but was not born after 1960.

SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
EXCEPT
SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    BIRTH_DATE > '1960-12-31'
ORDER BY 1

The result is:

PLAYERNO  BIRTH_DATE
--------  ----------
       2  1948-09-01
      39  1956-10-29
      83  1956-11-11

Explanation Each of the two table expressions returns a table with two columns and zero or more rows. The EXCEPT operator looks first for all rows appearing in the first table expression. These are the following players:

PLAYERNO  BIRTH_DATE
--------  ----------
       6  1964-06-25
      83  1956-11-11
       2  1948-09-01
       7  1963-05-11
      57  1971-08-17
      39  1956-10-29
     100  1963-02-28

Next, the operator looks for all the rows appearing in the second table expression:

PLAYERNO  BIRTH_DATE
--------  ----------
     112  1963-10-01
       8  1962-07-08
     100  1963-02-28
      28  1963-06-22
       6  1964-06-25
      44  1963-01-09
      27  1964-12-28
     104  1970-05-10
       7  1963-05-11
      57  1971-08-17

Finally, all rows appearing in the first intermediate result but not in the second are recorded in the end result. The end result of the entire statement is, of course, one table again.

The previous statement could also have been formulated as follows:

SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
AND      NOT(BIRTH_DATE > '1960-12-31')
ORDER BY 1

Just as with the UNION and INTERSECT operators, the same rules that were described in Section 13.3 apply when using the EXCEPT operator.

However, it is not always possible to replace the EXCEPT operator the way we did earlier. If the rows come from different tables, the trick does not work.

Figure. Get the player number and name of each player who has incurred at least one penalty and is not a captain.

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES
         EXCEPT
         SELECT   PLAYERNO
         FROM     TEAMS)

The result is:

PLAYERNO  NAME
--------  ---------
       8  Newcastle
      44  Baker
     104  Moorman

Theoretically, the existence of the EXCEPT operator makes the INTERSECT operator superfluous. Work out for yourself that the following two statements produce the same result under all circumstances.

SELECT   PLAYERNO
FROM     TEAMS
INTERSECT
SELECT   PLAYERNO
FROM     PENALTIES

SELECT   PLAYERNO
FROM     TEAMS
EXCEPT  (SELECT  PLAYERNO
         FROM    TEAMS
         EXCEPT
         SELECT  PLAYERNO
         FROM    PENALTIES)

Figure. Get the numbers of the players from the PENALTIES tables minus numbers 6, 27, and 58.

SELECT   PLAYERNO
FROM     PENALTIES
EXCEPT
SELECT   6 UNION SELECT 27 UNION SELECT 58

The result is:

PLAYERNO
--------
       8
      44
     104

Portability

Some SQL products use the term MINUS instead of EXCEPT.


Exercise 13.7:

Get the numbers of the players who have ever been committee members and for whom no penalties have been incurred.

Exercise 13.8:

Which payment numbers between 1 and 20 are not currently in use?



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows