The EXCEPT clause allows results of one result table to be subtracted from another result table.
Show all cities that are found in the table 'hotel', but are not contained in the result table of 'customer'.
SELECT city
FROM hotel
WHERE zip < 50000
EXCEPT
SELECT city
FROM customer
WHERE zip < 50000
The sequence of SELECT statements is not arbitrary, unlike for UNION and INTERSECT.
If all result rows are to be retrieved that are contained in 'customer' but not in 'hotel', the message 'Row not found' is output. The cities Dallas and New York have a counter part in the table 'hotel'. Therefore they are not returned. In this case, it is not important that the customer table contains one more entry for New York than the table 'hotel'.
Before EXCEPT comes into effect, an implicit DISTINCT is issued on the tables.
If each occurrence of the rows found in the particular result table are to be taken into account, EXCEPT ALL must be specified.
SELECT city
FROM customer
WHERE zip < 50000
EXCEPT ALL
SELECT city
FROM hotel
WHERE zip < 50000
'ALL' has prevented DISTINCT from being issued in this case as well. As 'NY' occurs once more in the table 'customer' than in the table 'hotel', that value is kept as the result.
To get a clear notion of this statement, one could think of the coinciding values of both tables be 'checked off', the remaining values of the first table forming the result.
In our example:
Two entries of 'Dallas' 'neutralize each other'.