Join is one of the most familiar of all of the relational operators. In practice, however, it turns out that queries that require the join operator at all often really require an extended form of that operator called semijoin (you might not have heard of semijoin before, but in fact it’s quite important). Here’s the definition:

In other words, r1 MATCHING r2 is the join of r1 and r2, projected back on the attributes of r1 (and so the heading of the result is the same as that of r1). Here’s an example (“Get suppliers who currently supply at least one part”):

Note that the expressions r1 MATCHING r2 and r2 MATCHING r1 aren’t equivalent, in general—the first returns some subset of r1, the second returns some subset of r2. Note too that we could replace IN by MATCH in the SQL version; interestingly, however, we can’t replace NOT IN by NOT MATCH in the semidifference analog (see below), because there’s no “NOT MATCH” operator in SQL.

Turning now to semidifference: If semijoin is in some ways more important than join, a similar remark applies here also, but with even more force—in practice, most queries that require difference at all really require semidifference.^[92] Here’s the definition:

Here’s an example (“Get suppliers who currently supply no parts at all”):

As with MATCHING, the heading of the result is the same as that of r1. Note: If r1 and r2 are of the same type, r1 NOT MATCHING r2 degenerates to r1 MINUS r2; in other words, difference (MINUS) is a special case of semidifference, relationally speaking. By contrast, join isn’t a special case of semijoin—they’re really different operators, though it’s true that (loosely speaking) some joins are semijoins and some semijoins are joins. See Exercise 7.19 at the end of the chapter.