The Join Operation

Joining two or more tables is the process of comparing the data in specified columns and using the comparison results to form a new table from the rows that qualify. A join statement:

Specifies a column from each table.
Compares the values in those columns row by row.
Combines rows with qualifying values into new rows.

Although the comparison is usually for equality ¾ values that match exactly ¾ other types of joins can also be specified. This join example finds the names of authors and publishers located in the same city:

SELECT au_fname, au_lname, pub_name
FROM authors, publishers
WHERE authors.city = publishers.city


au_fname	au_lname	pub_name
--------	-------	--------------------
Cheryl	Carson	Algodata Infosystems
Abraham	Bennet	Algodata Infosystems

(2 row(s) affected)

Since the query draws on information contained in two separate tables, publishers and authors, a join is required to retrieve the requested information.