Equijoins and Natural Joins

Joins that are based on equality (=) are called equijoins. Equijoins compare the values in the columns being joined for equality and then include all the columns in the tables being joined in the results.

This query is an example of an equijoin:

select * 
from authors, publishers 
where authors.city = publishers.city

In the results of this statement, the city column appears twice. By definition, the results of an equijoin contain two identical columns. Because there is usually no point in repeating the same information, one of these columns can be eliminated by restating the query. The result is called a natural join.

The query that results in the natural join of publishers and authors on the city column is:

select publishers.pub_id, publishers.pub_name, 
  publishers.state, authors.* 
from publishers, authors 
where publishers.city = authors.city

The column publishers.city does not appear in the results.

Another example of a natural join is:

select au_fname, au_lname, pub_name 
from authors, publishers 
where authors.city = publishers.city

You can use more than one join operator to join more than two tables, or to join more than two pairs of columns. These “join expressions” are usually connected with and, although or is also legal.

Following are two examples of joins connected by and. The first lists information about books (type of book, author, and title), ordered by book type. Books with more than one author have multiple listings, one for each author.

select type, au_lname, au_fname, title 
from authors, titles, titleauthor 
where authors.au_id = titleauthor.au_id 
and titles.title_id = titleauthor.title_id 
order by type

The second finds the names of authors and publishers that are located in the same city and state:

select au_fname, au_lname, pub_name 
from authors, publishers 
where authors.city = publishers.city 
and authors.state = publishers.state