Joined Relations

Joined Relations∗∗

SQL provides not only the basic Cartesian-product mechanism for joining tuples of relations found in its earlier versions, but, SQL also provides various other mechanisms

for joining relations, including condition joins and natural joins, as well as various forms of outer joins. These additional operations are typically used as subquery expressions in the from clause.

Examples

We illustrate the various join operations by using the relations loan and borrower in Figure 4.1. We start with a simple example of inner joins. Figure 4.2 shows the result of the expression loan inner join borrower on loan.loan-number = borrower .loan-number The expression computes the theta join of the loan and the borrower relations, with the join condition being loan.loan-number = borrower.loan-number. The attributes of the result consist of the attributes of the left-hand-side relation followed by the attributes of the right-hand-side relation.

Note that the attribute loan-number appears twice in the figure — the first occurrence is from loan, and the second is from borrower. The SQL standard does not require attribute names in such results to be unique. An as clause should be used to assign unique names to attributes in query and subquery results.

We rename the result relation of a join and the attributes of the result relation by using an as clause, as illustrated here:

loan inner join borrower on loan.loan-number = borrower.loan-number

as lb(loan-number, branch, amount, cust, cust-loan-num)

We rename the second occurrence of loan-number to cust-loan-num. The ordering of the attributes in the result of the join is important for the renaming.

Next, we consider an example of the left outer join operation:

We can compute the left outer join operation logically as follows. First, compute the result of the inner join as before. Then, for every tuple t in the left-hand-side relation loan that does not match any tuple in the right-hand-side relation borrower in the inner join, add a tuple r to the result of the join: The attributes of tuple r that are derived from the left-hand-side relation are filled in with the values from tuple t, and the remaining attributes of r are filled with null values. Figure 4.3 shows the resultant relation. The tuples (L-170, Downtown, 3000) and (L-230, Redwood, 4000) join with tuples from borrower and appear in the result of the inner join, and hence in the result of the left outer join. On the other hand, the tuple (L-260, Perryridge, 1700) did not match any tuple from borrower in the inner join, and hence a tuple (L-260, Perryridge, 1700, null, null) is present in the result of the left outer join.

Finally, we consider an example of the natural join operation:

loan natural inner join borrower

This expression computes the natural join of the two relations. The only attribute name common to loan and borrower is loan-number. Figure 4.4 shows the result of the expression. The result is similar to the result of the inner join with the on condition in Figure 4.2, since they have, in effect, the same join condition. However, the attribute loan-number appears only once in the result of the natural join, whereas it appears twice in the result of the join with the on condition.

Join Types and Conditions

In Section 4.10.1, we saw examples of the join operations permitted in SQL. Join operations take two relations and return another relation as the result. Although outer- join expressions are typically used in the from clause, they can be used anywhere that a relation can be used.

Each of the variants of the join operations in SQL consists of a join type and a join condition. The join condition defines which tuples in the two relations match and what attributes are present in the result of the join. The join type defines how tuples in each

relation that do not match any tuple in the other relation (based on the join condition) are treated. Figure 4.5 shows some of the allowed join types and join conditions. The first join type is the inner join, and the other three are the outer joins. Of the three join conditions, we have seen the natural join and the on condition before, and we shall discuss the using condition, later in this section.

The use of a join condition is mandatory for outer joins, but is optional for inner joins (if it is omitted, a Cartesian product results). Syntactically, the keyword natural appears before the join type, as illustrated earlier, whereas the on and using conditions appear at the end of the join expression. The keywords inner and outer are optional, since the rest of the join type enables us to deduce whether the join is an inner join or an outer join.

The meaning of the join condition natural, in terms of which tuples from the two relations match, is straightforward. The ordering of the attributes in the result of a natural join is as follows. The join attributes (that is, the attributes common to both relations) appear first, in the order in which they appear in the left-hand-side relation. Next come all nonjoin attributes of the left-hand-side relation, and finally all nonjoin attributes of the right-hand-side relation.

The right outer join is symmetric to the left outer join. Tuples from the right-hand- side relation that do not match any tuple in the left-hand-side relation are padded with nulls and are added to the result of the right outer join.

Here is an example of combining the natural join condition with the right outer join type:

loan natural right outer join borrower

Figure 4.6 shows the result of this expression. The attributes of the result are defined by the join type, which is a natural join; hence, loan-number appears only once. The first two tuples in the result are from the inner natural join of loan and borrower. The tuple (Hayes, L-155) from the right-hand-side relation does not match any tuple from the left-hand-side relation loan in the natural inner join. Hence, the tuple (L-155, null, null, Hayes) appears in the join result.

The join condition using(A1, A2,..., An) is similar to the natural join condition, except that the join attributes are the attributes A1, A2,..., An, rather than all attributes that are common to both relations. The attributes A1, A2,... , An must consist of only attributes that are common to both relations, and they appear only once in the result of the join.

The full outer join is a combination of the left and right outer-join types. After the operation computes the result of the inner join, it extends with nulls tuples from

the left-hand-side relation that did not match with any from the right-hand-side, and adds them to the result. Similarly, it extends with nulls tuples from the right-hand- side relation that did not match with any tuples from the left-hand-side relation and adds them to the result.

For example, Figure 4.7 shows the result of the expression

loan full outer join borrower using (loan-number)

As another example of the use of the outer-join operation, we can write the query “Find all customers who have an account but no loan at the bank” as

SQL-92 also provides two other join types, called cross join and union join. The first is equivalent to an inner join without a join condition; the second is equivalent to a full outer join on the “false” condition — that is, where the inner join is empty.