join : Java Glossary

*0-9ABCDEFGHIJKLMNOPQRSTUVWXYZ (all)

join
In SQL, selecting a row created from a data in two different tables, matched up by comparing values in specified columns. Here is a simple example of logically joining two table together by a common key:
-- 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.


This page is posted
on the web at:

http://mindprod.com/jgloss/join.html

Optional Replicator mirror
of mindprod.com
on local hard disk J:

J:\mindprod\jgloss\join.html
logo
Please the feedback from other visitors, or your own feedback about the site.
Contact Roedy. Please feel free to link to this page without explicit permission.

IP:[65.110.21.43]
Your face IP:[54.204.104.71]
You are visitor number