SQL Series...29

Relational Operators

EXCEPT

The UNION operation acts on two source tables and returns all rows that appear in either table. The INTERSECT operation returns all rows that appear in both the first and the second table. In contrast, the EXCEPT (or EXCEPT DISTINCT) operation returns all rows that appear in the first table but that do not also appear in the second table.

Returning to the municipal pager database example, say that a group of pagers that had been declared out of service and returned to the vendor for repairs have now been fixed and placed back into service. The PAGERS table was updated to reflect the returned pagers, but the returned pagers were not removed from the OUT table as they should have been. You can display the PagerID numbers of the pagers in the OUT table, with the reactivated ones eliminated, using an EXCEPT operation:

SELECT *

FROM OUT

EXCEPT CORRESPONDING (PagerID)

SELECT *

FROM PAGERS;

This query returns all the rows in the OUT table whose PagerID is not also present in the PAGERS table.