join : Java Glossary

-- t1 and t2 are tables.
-- The result shows fields from
-- both t1 and t2 together in a single record.
SELECT * FROM t1 LEFT JOIN t2 ON (t2.a=t1.a);

inner join

contain only matches from the two SQL (Standard Query Language) tables. If not specified, inner join is presumed.

outer join

contain mismatches as well as matches from the two SQL tables.

left outer join

contains entries from table A, whether or not they had matches in table B.

right outer join

contains entries from table B, whether or not they had matches in table A.

natural join

a join where all identically named columns in tables A and B have matching values.

keyed join

a join done using matching values for fields in the PRIMARY and FOREIGN KEYS in the natural way.

cross join

a Cartesian product of table A with table B, creating a combined record for every possible combination of a record from A with one from B.

self join

It is possible to join a table to itself, creating Cartesian product list of all combinations of pairs of records matching some criteria.

straight join

is identical to a join. except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

Tips

Even when you want only one record, you can define a set of joined records and add a WHERE clause to pick out the one you want. SQL will be clever. It will not actually construct the set. That is just how it works conceptually.

You can compose inner joins without even using an explicit JOIN keyword.

SELECT weather.city, weather.temp_lo, weather.temp_hi,
    weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

Outstanding Questions

Consider tables of authors and quotations. Each author may have many quotations, but each quotation can have only one author.  Which table should be A or B, or does it make any difference? I suspect it does not matter. The engine can figure out which is better on its own. I would hope the engine would be smart enough to fetch the quotation record first then find the corresponding author, rather than trying to find all the quotes for each author. You can always try it both ways.