The Natural Join

In our example query that combined information from the instructor and teaches
table, the matching condition required instructor.ID to be equal to teaches.ID. These
are the only attributes in the two relations that have the same name. In fact this
is a common case; that is, the matching condition in the from clause most often
requires all attributes with matching names to be equated.

To make the life of an SQL programmer easier for this common case, SQL
supports an operation called the natural join, which we describe below. In fact SQL
supports several other ways in which information from two or more relations
can be joined together. We have already seen how a Cartesian product along
with a where clause predicate can be used to join information from multiple
relations. Other ways of joining information from multiple relations are discussed
in later topics.

The natural join operation operates on two relations and produces a relation
as the result. Unlike the Cartesian product of two relations, which concatenates
each tuple of the first relation with every tuple of the second, natural join considers
only those pairs of tuples with the same value on those attributes that appear in
the schemas of both relations. So, going back to the example of the relations
instructor and teaches, computing instructor natural join teaches considers only
those pairs of tuples where both the tuple from instructor and the tuple from
teaches have the same value on the common attribute, ID.

The result relation, shown in Figure 3.8, has only 13 tuples, the ones that
give information about an instructor and a course that that instructor actually
teaches. Notice that we do not repeat those attributes that appear in the schemas
of both relations; rather they appear only once. Notice also the order in which the
attributes are listed: first the attributes common to the schemas of both relations,
second those attributes unique to the schema of the first relation, and finally, those
attributes unique to the schema of the second relation.

Consider the query “For all instructors in the university who have taught
some course, find their names and the course ID of all courses they taught”,
which we wrote earlier as:

select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID;

This query can be written more concisely using the natural-join operation in
SQL as:

select name, course_id
from instructor natural join teaches;

Both of the above queries generate the same result.

As we saw earlier, the result of the natural join operation is a relation. Conceptually, expression “instructor natural join teaches” in the from clause is replaced by the relation obtained by evaluating the natural join. The where and select
clauses are then evaluated on this relation, as we saw in earlier topics.

A from clause in an SQL query can have multiple relations combined using
natural join, as shown here:

select A1, A2,..., An
from r1 natural join r2 natural join ... natural join r_m
where P;

More generally, a from clause can be of the form

from E1, E2,..., En

where each Ei can be a single relation or an expression involving natural joins.
For example, suppose we wish to answer the query “List the names of instructors
along with the the titles of courses that they teach.” The query can be written in
SQL as follows:

select name, title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;

The natural join of instructor and teaches is first computed, as we saw earlier, and
a Cartesian product of this result with course is computed, from which the where
clause extracts only those tuples where the course identifier from the join result
matches the course identifier from the course relation. Note that teaches.course id
in the where clause refers to the course_id field of the natural join result, since this
field in turn came from the teaches relation.

In contrast the following SQL query does not compute the same result:

select name, title
from instructor natural join teaches natural join course;

To see why, note that the natural join of instructor and teaches contains the attributes (ID, name, dept_name, salary, course_id, sec_id), while the course relation contains the attributes (course_id, title, dept_name, credits). As a result, the natural join of these two would require that the dept_name attribute values from the two inputs be the same, in addition to requiring that the course_id values be the same. This query would then omit all (instructor name, course title) pairs where the instructorteaches a course in a department other than the instructor’s own department.The previous query, on the other hand, correctly outputs such pairs.

To provide the benefit of natural join while avoiding the danger of equating
attributes erroneously, SQL provides a form of the natural join construct that
allows you to specify exactly which columns should be equated. This feature is
illustrated by the following query:

select name, title
from (instructor natural join teaches) join course using (course_id);

The operation join ... using requires a list of attribute names to be specified. Both
inputs must have attributes with the specified names. Consider the operation r1
join r2 using(A1, A2). The operation is similar to r1 natural join r2, except that a
pair of tuples t1 from r1 and t2 from r2 match if t1.A1 = t2.A1 and t1.A2 = t2.A2; even if r1 and r2 both have an attribute named A3, it is not required that t1.A3 = t2.A3.

Thus, in the preceding SQL query, the join construct permits teaches.dept_name
and course.dept_name to differ, and the SQL query gives the correct answer.

Frequently Asked Questions