FROM/WHERE Join Syntax

A skeleton version of FROM/WHERE join syntax is this:

SELECT select_list
FROM table_1, table_2 [, table_3]...
						WHERE [table_1.]column  join_operator  [table_2.]column
					

The join operator in the first several examples is the equal sign (=). The FROM clause's table list must include at least two tables, and the columns specified in the WHERE clause must be join-compatible. When the join columns have identical names, you must qualify the columns with their table names in the select list and in the WHERE clause.

For example, if you wanted to know the names and positions of the editors of Secrets of Silicon Valley (PC8888), you'd need a join on a column that exists in the two relevant tables:

select ed_lname, ed_fname, ed_pos
from editors, titleditors
where editors.ed_id = titleditors.ed_id
   and titleditors.title_id = 'PC8888'
ed_lname                  ed_fname           ed_pos
========================= ================== ===========
DeLongue                  Martinella         project
Samuelson                 Bernard            project
Kaspchek                  Christof           acquisition
[3 rows]

This join selects the names of editors who are connected to the title_id “PC8888” by joining on the ed_id column that appears in both the editors and the titleditors tables. Note that the join columns need not be included in the SELECT list.