Database: SELECT: Joins - Inner cross joins
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.
Cross joins
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 |
| = |
| field_1 | field_2 | field_x | field_y |
| A | B | 1 | 2 |
| A | B | 3 | 4 |
| A | B | 5 | 6 |
| C | D | 1 | 2 |
| C | D | 3 | 4 |
| C | D | 5 | 6 |
|
This produces a lot of meaningless combinations, so there are ways to select
only the rows that make meaningful combinations of records.
Inner joins - records with equal keys
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.
Selecting the rows
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 field,...
FROM t1 INNER JOIN t2 ON t1.primary_key = t2.foreign_key;