SQL Series...28

Relational Operators

INTERSECT

The UNION operation produces a result table containing all rows that appear in any of the source tables. If you want only rows that appear in all the source tables, you can use the INTERSECT operation, which is the SQL implementa-tion of relational algebra’s intersect operation. I illustrate INTERSECT by return-ing 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 opera-tion’s result table:

SELECT *

FROM NATIONAL

INTERSECT

SELECT *

FROM AMERICAN;

FirstName LastName CompleteGames

--------- -------- -------------

Bob Turley 8

The result table tells us that Bob Turley was the only pitcher to throw the same number of complete games in both leagues. (A rather obscure distinc-tion for old Bullet Bob.) Note that, 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 need not be union-compatible, although the corresponding columns need to have matching types and lengths.

Consider another example: A municipality keeps track of the pagers carried by police officers, firefighters, street sweepers, and other city employees. A database table called PAGERS contains data on all pagers in active use. Another table named OUT, with an identical structure, contains data on all pagers that have been taken out of service. No pager should ever exist in both tables. With an INTERSECT operation, you can test to see whether such an unwanted duplication has occurred:

SELECT *

FROM PAGERS

INTERSECT CORRESPONDING (PagerID)

SELECT *

FROM OUT ;

If the result table contains any rows, you know you have a problem. You should investigate any PagerID entries that appear in the result table. The corresponding pager 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.