Discovering SQL: A Hands-On Guide for Beginners

BUT WAIT; THERE'S MORE!

So far, we were JOIN(ing) and UNION(ing) our data sets in every way imaginable, but set theory as implemented in SQL has a few more tricks up its sleeve: INTERSECT and EXCEPT. Both of these were endorsed by the SQL Standard and most RDBMSs.

INTERSECT

The INTERSECT operator is used on the results of two queries to include only the records produced by the first query that have the matching records in the second query. If you notice a similarity between subqueries and the EXISTS operator discussed in Chapters 2 and 6 you are absolutely correct. In many ways, they are the same.

For instance, to find out which books have been allocated places on a shelf in our bookcase, we could run the following query:

SELECT bk_id FROM  books
    INTERSECT
SELECT fk_bk_loc FROM location;
bk_id
--------------------
1
2
3
4
5
6
7
8
9
10
11
12

(12 row(s) affected)

The result is 12 records: the exact number of books we have in the BOOKS table, all assigned to some place in the bookcase. Had we entered a new book into the table and not created a record for it in the LOCATION table, this new book would not show up in the tally (in fact, we will try this out at the end of this section).

The reasons for using INTERSECT is code maintainability. The intent is clearer when using the operator as opposed to the subquery. For all we know, RDBMSs might treat them both in exactly the same way behind the scenes.

EXCEPT and MINUS

The goal that is the Holy Grail of database programming is to minimize ...