An equi join is a join implying only the operation equal, =.
A Cartesian join, or Cross join, produces a result where each row of the
first table is horizontally merged with each row of the second table.
An inner join is logically a Cartesian join where only produced merged-rows
satisfy the ON clause.
A left join is logically an inner join, except that, if, for a row in the
first table, there will be no merged-rows satisfying the ON clause, the row,
of the first table, is re-introduced into the result, with NULL values for
the associated rows that would have come from the merge.
A right join is logically a left join where the two tables exchange their
role.
So, if I have a table, dices, one field, d, with values from 1 to 6 (in 6
different rows), then
SELECT a.d, b.d
FROM dices AS a, dices AS b
which is a Cartesian join, will produce 36 rows, where each row, in the
result, can represent a possible outcome of 'throwing two dices'
SELECT a.d, b.d
FROM dices AS a INNER JOIN dices AS b
ON a.d = b.d
is an inner join and an equi-join, It produces 6 rows, ie, all the 'double'
when you throw two dices.
SELECT a.d, b.d
FROM dices AS a INNER JOIN dices AS b
ON a.d < b.d
is an inner join, but not an equi join, since it uses < , not =, as
operator. It may simulate cases where the first dice has to be strictly
less than the second dice. Note that there is no value 6 under the first
column.
SELECT a.d, b.d
FROM dices AS a LEFT JOIN dices AS b
ON a.d < b.d
is the same as the previous query, except that it re-introduces the
possibility that you can have a 6 has first dice: { 6, NULL }, but
definitively, there is no, null, possibility for the second dice to be
strictly greater than the 6 we got for the first dice, in THAT specific
case.
Hoping it may help,
Vanderghast, Access MVP
