The final relational query operator is “join”. The join operator is used to combine information from multiple tables together into a single table, based on some attribute that the tables share in common. The number of columns (attributes) in the resulting table will always be the sum of the number of attributes in both tables minus one. The reason for this is that all attributes in the two source tables will be included in the resulting table, but since one of the attributes is shared, it will only be listed once.

The number of rows contained in the joined table will vary depending on the type of join and the data present in the tables. The most common type of join is an “Equijoin”. In an equijoin, the shared attribute in one row (tuple) of the first table must exactly match the same attribute in some row of the second table in order for a tuple (formed from these two rows) to be included in the resulting table.

An example will make this clear. Consider the two tables, Alpha and Beta, shown in . These two tables share attribute “Z” in common and so may be joined. Since Alpha has three columns and Beta also has three columns, the resulting table will have five columns (3 + 3 – 1). Every tuple of Alpha will be compared to every tuple of Beta. Assuming we are performing an equijoin, those tuples in which Alpha.Z equals Beta.Z will be joined together to form a tuple in the resulting relation. So, for example, since the “Z” in row one of Alpha matches the “Z” in row one of Beta, they will form a tuple in the combined relation. Row one of Alpha with row two of Beta also forms a tuple. As does row three of Alpha with row four of Beta. Since no other tuples satisfy the specified condition, the relation resulting from this join contains only these three tuples.

A join of relations Alpha and Beta over attribute “Z”

The join of Alpha and Beta over equal Z values could be expressed as :

Join Alpha and Beta where Alpha.Z = Beta.Z

Further, if we agree that whenever we use the word “join” we really mean “equijoin” the expression can be simplified to:

Join Alpha and Beta over Z

The general format used to specify joins in the remainder of this chapter is:

Join relation1 and relation2 over shared_attribute

where the fields “relation1” and “relation2” will be replaced by the names of existing relations and “shared_attribute” will be replaced by the name of the attribute they share in common.