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.
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.
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.
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.
Some SQL products use the term MINUS instead of EXCEPT.