SQL and Relational Theory, 3rd Edition

Chapter 7

SQL and Relational Algebra II: Additional Operators

Algebra is the part of advanced mathematics that is not calculus.

—John Derbyshire: Unknown Quantity: A Real and Imaginary History of Algebra (2006)

As I’ve said several times already, a relational algebra operator is an operator that (a) takes one or more relations—or possibly no relations at all, in the case of the n-adic versions of operators such as join (see Chapter 6)—as input and (b) produces another relation as output. As I observed in Chapter 1, however, any number of operators can be defined that conform to this simple characterization. The previous chapter described the original operators (join, project, etc.); by contrast, the present chapter describes some of the many additional operators that have been defined since the relational model was first invented. It also considers how those operators might best be realized in SQL.

Note: By its nature, this chapter is necessarily something of a miscellany. Thus, you might just want to skim it lightly on a first pass, and come back to it later if you need to gain a deeper understanding of one or more of the topics discussed. Perhaps it would help to say up front that, from a practical point of view at least, the most important topics are probably these:

• Semijoin and semidifference (MATCHING and NOT MATCHING)

• EXTEND

• Image relations

• Aggregate operators

But I’ll begin with a brief discussion of exclusive union.

EXCLUSIVE UNION

In set theory, union is inclusive ...