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 relationsr1andr2be joinable. Then theirnatural join(or justjoinfor short),r1JOINr2, is a relation with (a) heading the set theory union of the headings ofr1andr2and (b) body the set of all tuplestsuch thattis the set theory union of a tuple fromr1and a tuple fromr2.

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 ...

Start Free Trial

No credit card required