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 Natural Join

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

Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
Ans: The basic structure of an SQL query consists of three clauses: select, from, and where. view more..
Ans: The set of relations in a database must be specified to the system by means of a data-definition language (DDL). view more..
Ans: The natural join operation operates on two relations and produces a relation as the result. view more..
Ans: Reason to rename a relation is a case where we wish to compare tuples in the same relation. view more..
Ans: SQL permits a variety of functions on character strings. Read to know about them. view more..
Ans: The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.  A department controls a number of projects, each of which has a unique name, a unique number, and a single location view more..
Ans: Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) view more..
Ans: A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribut view more..
Ans: An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique view more..
Ans: There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute view more..
Ans: Select clause uses. view more..
Ans: Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types view more..
Ans: If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints view more..
Ans: The individual entity instances in an entity set and the individual relationship instances in a relationship set. In ER diagrams the emphasis is on representing the schemas rather than the instances. view more..
Ans: The UML methodology is being used extensively in software design and has many types of diagrams for various software design purposes. We only briefly present the basics of UML class diagrams here, and compare them with ER diagrams view more..
Ans: we defined the degree of a relationship type as the number of participating entity types and called a relationship type of degree two binary and a relationship type of degree three ternary view more..
Ans: SQL offers the user some control over the order in which tuples in a relation are displayed. view more..

Rating - 3/5