Open map List

Join

Introduction

The join operator takes two input relations and produces one output relation, gluing join operator two tuples together (one from each input relation) to form a bigger tuple—provided they meet a specified condition.

 

Explanation

Figure 1: The essential binary query operator: join. The join condition for this example is TitleDeed.Plot = ParcelPid, which expresses a foreign key/key link between TitleDeed and Parcel. The
result relation has 3 + 3 = 6 attributes.

The join query for our example is easily expressed in SQL as:
SELECT ?
FROM TitleDeed, Parcel
WHERE TitleDeed.Plot = Parcel.PId
The FROM clause identifies the two input relations; the WHERE clause states the join condition.

It is often not sufficient to use just one operator for extracting sensible information from a database. The strength of the above operators is hidden in the fact that they can be combined to produce more advanced and useful query definitions. A final example illustrates this. Take another look at the join of Figure 1. Suppose we really wanted to obtain combined TitleDeed/Parcel information, but only for parcels with a size over 1000, and we only wanted to see the owner identifier and deed date of such title deeds.

We can take the result of the join above and select the tuples that show a parcel size over 1000. The result of this tuple selection can then be taken as the input for an attribute selection that only leaves Owner and DeedDate. This is illustrated in Figure 2.

Figure 2: A combined selection/projection/join query for selecting owners and deed dates for parcels with a size larger than 1000. The join is done first, then follows a tuple selection on the resulting tuples of the join, which is completed by an attribute projection.


Finally, we may look at the SQL statement that would give us the query of Figure 2.
It can be written as:
SELECT Owner
DeedDate FROM TitleDeed
Parcel WHERE TitleDeed.Plot = Parcel.PId AND AreaSize > 1000

 

Outgoing relations

Learning paths