Natural Joins / Outerjoins

Natural Joins / Outerjoins

Natural Joins

As we recall from "Natural Joins / Theta-Joins", a natural join differs from a theta-join in that:

1. The join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no
other conditions.

2. One of each pair of equated attributes is projected out.

The SQL natural join behaves exactly this way. Keywords NATURAL JOIN appear between the relations to express the  operator.

Example 1 : Assume we want to compute the natural join of the relations

The result will be a relation whose schema includes attributes name and address plus all the attributes that appear in one or the other of the two relations. A tuple of the result will represent an individual who is both a star and an executive and will have all the information pertinent to either: a name, address, gender, birthdate, certificate number, and net worth. The expression

MovieStar NATURAL JOIN MovieExec;

briefly describes the desired relation.


The outerjoin operator was introduced in "Outerjoins" as a way to augment the result of a join by the dangling tuples, padded with null values. In SQL we can specify an outerjoin; NULL is used as the null value.

Example 2 : Assume we wish to take the outerjoin of the two relations

SQL refers to the standard outerjoin, which pads dangling tuples from both of its arguments, as a full outerjoin. The syntax is unsurprising:


The result of this operation is a relation with the same six-attribute schema as Example 1. The tuples of this relation are of three kinds. Those representing individuals who are both stars and executives have tuples with all six attributes non-NULL. These are the tuples that are also in the result of Example 1.

The second kind of tuple is one for an individual who is a star but not an executive. These tuples have values for attributes name, address, gender and birthdate taken from their tuple in MovieStar, while the attributes belonging only to MovieExec, namely cert# and netWorth, have NULL values.

The third kind of tuple is for an executive who is not also a star. These tuples have values for the attributes of MovieExec taken from their MovieExec tuple and NULL's in the attributes gender and birthdate that come only from MovieStar. For example, the three tuples of the result relation illustrated in Figure 1 correspond to the three types of individuals, respectively.

Three tuples in the outerjoin of MovieStar and MovieExec

All the variations on the outerjoin that we mentioned in "Outerjoins" are also available in SQL. If we want a left- or right-outerjoin, we add the appropriate word LEFT or RIGHT in place of FULL. For instance,


would yield the first two tuples of Figure 1 but not the third. Similarly,


would yield the first and third tuples of Figure 1 but not the second.

Next, assume we want a theta-outerjoin, instead of a natural outerjoin. Instead of using the keyword NATURAL, we may follow the join by ON and a condition that matching tuples must obey. If we also specify FULL OUTER JOIN, then after matching tuples from the two joined relations, we pad dangling tuples of either relation with NULL's and include the padded tuples in the result.

Example 3 : Let us reconsider "Subqueries in FROM Clauses" Example 2, where we joined the relations Movie and StarsIn using the conditions that the title and movieTitle attributes of the two relations agree and that the year and movieYear attributes of the two relations agree. If we modify that example to call for a full outerjoin:

then we shall get not only tuples for movies that have at least one star mentioned in StarsIn, but we shall get tuples for movies  with no listed stars, padded with NULL's in attributes movieTitle, movieYear and starName. Likewise, for stars not appearing in any movie listed in relation Movie we get a tuple with NULL's in the six attributes of Movie.

The keyword FULL can be replaced by either LEFT or RIGHT in outerjoins of the type suggested by Example 3. For example,

gives us the Movie tuples with at least one listed star and NULL-padded Movie tuples without a listed star, but will not include  stars without a listed movie. Conversely,

will omit the tuples for movies without a listed star but will include tuples for stars not in any listed movies, padded with NULL's.