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