Analyzing a Join

The join operator, which expresses the relationship between the join columns, can be any of the relational operators. Equality is the most common (in which a value in the join column from one table is equal to a value in the join column from another table). For example, if an author calls to ask which editors live in the same city as a publisher's home office, a join query can provide the answer. The following query finds the names of editors who live in the same city as Algodata Infosystems:

select ed_lname, ed_id, editors.city, pub_name, publishers.city
from editors, publishers
where editors.city = publishers.city
  and pub_name = 'Algodata Infosystems'
ed_lname   ed_id         city         pub_name               city
========== ============= ============ ====================== ========
DeLongue   321-55-8906   Berkeley     Algodata Infosystems   Berkeley
Kaspchek   943-88-7920   Berkeley     Algodata Infosystems   Berkeley
[2 rows]

A SQL-92 version of the query is

select ed_lname, ed_id, editors.city, pub_name, publishers.city
from editors join publishers
						on editors.city = publishers.city
where pub_name = 'Algodata Infosystems'

The query, with either syntax, promptly reports that two editors, DeLongue and Kaspchek, live in the same city (Berkeley) as Algodata Infosystems. This was information you didn't know earlier. In fact, all you had to know was the publisher's name, and the join supplied you with all that additional information. You told the system: “Join on the city, whatever that is, and tell me the editors' names, whoever they are.”