How to Use the INTERSECT and EXCEPT Operations in SQL
The UNION operation produces a result table containing all rows that appear in any of the source SQL tables. If you want only rows that appear in all the source tables, you can use the INTERSECT operation, which is the SQL implementation of relational algebra’s intersect operation. INTERSECT can be illustrated by returning to the fantasy world in which Bob Turley was traded to the Dodgers in midseason:
SELECT * FROM NATIONAL; FirstName LastName CompleteGames --------- -------- ------------- Sal Maglie 11 Don Newcombe 9 Sandy Koufax 13 Don Drysdale 12 Bob Turley 8 SELECT * FROM AMERICAN; FIRST_NAME LAST_NAME COMPLETE_GAMES ---------- --------- -------------- Whitey Ford 12 Don Larson 10 Bob Turley 8 Allie Reynolds 14
Only rows that appear in all source tables show up in the INTERSECT operation’s result table:
SELECT * FROM NATIONAL INTERSECT SELECT * FROM AMERICAN; FirstName LastName CompleteGames --------- -------- ------------- Bob Turley 8
The result table tells you that Bob Turley was the only pitcher to throw the same number of complete games in both leagues (a rather obscure distinction for old Bullet Bob). Note: As was the case with UNION, INTERSECT DISTINCT produces the same result as the INTERSECT operator used alone. In this example, only one of the identical rows featuring Bob Turley is returned.
The ALL and CORRESPONDING keywords function in an INTERSECT operation the same way they do in a UNION operation. If you use ALL, duplicates are retained in the result table. If you use CORRESPONDING, the intersected tables don’t need to be union-compatible, although the corresponding columns must have matching types and lengths.
Here’s what you get with INTERSECT ALL:
SELECT * FROM NATIONAL INTERSECT ALL SELECT * FROM AMERICAN; FirstName LastName CompleteGames --------- -------- ------------- Bob Turley 8 Bob Turley 8
Consider another example: A municipality keeps track of the cell phones carried by police officers, firefighters, street sweepers, and other city employees. A database table called PHONES contains data on all phones in active use. Another table named OUT, with an identical structure, contains data on all phones that have been taken out of service.
No phones should ever exist in both tables. With an INTERSECT operation, you can test to see whether such an unwanted duplication has occurred:
SELECT * FROM PHONES INTERSECT CORRESPONDING (PhoneID) SELECT * FROM OUT ;
If this operation gives you a result table containing any rows at all, you know you have a problem. You should investigate any PhoneID entries that appear in the result table. The corresponding phone is either active or out of service; it can’t be both. After you detect the problem, you can perform a DELETE operation on one of the two tables to restore database integrity.
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 tables. 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 phone database example, say that a group of phones that had been declared out of service and returned to the vendor for repairs have now been fixed and placed back into service. The PHONES table was updated to reflect the returned phones, but the returned phones were not removed from the OUT table as they should have been.
You can display the PhoneID numbers of the phones in the OUT table, with the reactivated ones eliminated, using an EXCEPT operation:
SELECT * FROM OUT EXCEPT CORRESPONDING (PhoneID) SELECT * FROM PHONES;
This query returns all the rows in the OUT table whose PhoneID is not also present in the PHONES table.