O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

image with no caption

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required