SQL and Relational Theory, 2nd Edition

JOIN

Before I get to the join operator as such, it’s helpful to introduce the concept of “joinability.” Relations r1 and r2 are joinable if and only if attributes with the same name are of the same type (meaning they are in fact the very same attribute)—equivalently, if and only if the set theory union of the headings of r1 and r2 is itself a legal heading. Note that this concept applies not just to join as such but to various other operations as well, as we’ll see in the next chapter. Anyway, armed with this notion, I can now define the join operation (note how the definition appeals to the fact that tuples are sets and hence can be operated upon by set theory operators such as union):

Definition: Let relations r1 and r2 be joinable. Then their natural join (or just join for short), r1 JOIN r2, is a relation with (a) heading the set theory union of the headings of r1 and r2 and (b) body the set of all tuples t such that t is the set theory union of a tuple from r1 and a tuple from r2.

The following example is repeated from the section SOME PRELIMINARIES, except that now I’ve dropped the explicit name qualifiers in the SQL version where they aren’t needed:

I remind you, however, that SQL also allows this join to be expressed in an alternative style that’s a little closer to that of Tutorial D (and this time I deliberately replace that long commalist of column references in the SELECT ...