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 ...