Joins are the operation of putting together information that is spread across many tables. A normalized table design removes repeated fields and redundant information, distributing the information into many tables. The SELECT command can be used to join them together in several ways. The most common is the cross join.
The logical basis for joining two tables is to produce the cross join (also called the cartesian product), which is a new table with rows that make every possible combination of rows from the first table followed by rows in the second table. For example, given these two (meaningless) tables:
| X |
| = |
|
This produces a lot of meaningless combinations, so there are ways to select only the rows that make meaningful combinations of records.
But in most joins only the rows where the primary key in one table matches the foreign key in another table are wanted. The inner join does exactly this.
Altho a database system logically forms a cross product, the actual joining is much more efficient.
There are several ways, all valid, to write joins in SQL. See Join Syntax Variations. Microsoft Access generates the following for queries generated from its wizard:
SELECT column,...
FROM t1 INNER JOIN t2 ON t1.primary_key = t2.foreign_key;