Joins 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
