# Joins

R

#### RJF

I know this is a very simple question but...

I'm just learning SQL and have been going through a step by step book. I'm
confused about what the difference is between an equi-join and an inner join.

Are both the inner join and the outer join just 2 different types of
equi-joins? Or is the equi-join something different than both?

Thank you,

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

That answer was much more than I expected. Thank you so much for taking the
time to explain it so clearly.

It was very helpful and greatly appreciated.

Thanks,