SQL Pocket Guide, 2nd Edition

Joining Tables

Joins allow you to combine data from multiple tables into a single result-set row. There are two fundamental types of join: inner and outer. There are also two join syntaxes—the syntax introduced in the 1992 SQL standard, which depends on a JOIN clause, and an older syntax in which you separate table names with commas.

The Concept of a Join

The concept of a join is best explained by beginning with the earlier syntax. To join related rows from two tables, begin by listing two table expressions separated by a comma in your FROM clause. For example, to retrieve a list of waterfalls and their county names, you could begin by writing:

SELECT u.name AS fall, c.name AS county
FROM upfall u, county c;

FALL            COUNTY
--------------- ----------
Munising Falls  Alger
Munising Falls  Baraga
Munising Falls  Ontonogan
 . . .

This result is a Cartesian product, which is all possible combinations of rows from the two tables. Conceptually, all joins begin as Cartesian products. From there, it’s up to you to supply conditions to narrow down the results to only those rows that make sense. Using the older join syntax, you supply those conditions in the WHERE clause:

SELECT u.name AS fall, c.name AS county
FROM upfall u, county c
WHERE u.county_id = c.id;

FALL            COUNTY
--------------- ----------
Munising Falls  Alger
Tannery Falls   Alger
Alger Falls     Alger
 . . .

These results are much more useful.

The process I’ve just described is purely conceptual; database systems will rarely or never form a Cartesian ...