Men of judgment ... so enclose
Infinite riches in a little room
—Christopher Marlowe: The Jew of Malta (c. 1592)
As noted in Chapter 4, any number of operators can be defined that fit the simple definition of “one or more relations in, exactly one relation out.” That previous chapter described Codd’s original operators (join, project, etc.); in this chapter, by contrast, I want to describe some of the many additional operators that have been defined since the relational model was first invented. Specifically, I want to discuss (a) MATCHING and NOT MATCHING; (b) EXTEND; (c) image relations; and (d) aggregation, summarization, and related matters.
It turns out in practice that most relational expressions—not all—that seem to need the join operator for their formulation really need a related but logically distinct operator called semijoin (MATCHING, in Tutorial D). Here’s an example (the query is “Get supplier details for suppliers who supply at least one part”):
S MATCHING SP
Here’s the result:
This result contains just the tuples for suppliers that “match” at least one shipment.
Here now is the semijoin definition (note how it appeals to the concept of joinability, which was defined in Chapter 4):
Definition: Let relations r1 and r2 be joinable, and let r1 have attributes called A1, A2, ..., An (only). Then the expression r1 MATCHING r2 denotes the semijoin ...