Hi,

****************
A CROSS join matches all records of table a {f1, f2} with all records of
table b {f1, f2}. If table a has 3 records{{a, 1}, {a, 2}, {a,3}} and table
b has 2 records {{b, 1}, {b, 2}} then the result has 6 records:

a, 1, b, 1
a, 1, b, 2
a, 2, b, 1
a, 2, b, 2
a, 3, b, 1
a, 3, b, 2

ie, you can find a row, in this result, that owns any row you can pick from
a and any row you pick from b,

*****************
An INNER join add a condition (like a WHERE, but expressed in the join after
the keyword ON ) and ONLY the result form the CROSS join that passes the ON
condition are kept. As example, with

ON  a.f2 <=  b.f2

only 3 rows will be returned, since  {a, 2} {b, 1}  does not pass the test,
neither  {a, 3}{b,1}, neither {a, 3}{b,2}.

a, 1, b, 1
a, 1, b, 2
a, 2, b, 2

*****************
An OUTER join is like an INNER join but IF a row, from the preserved table,
completely disappear, it is re-introduced, with NULL under the columns of
the other table.

FROM a LEFT JOIN b ON a.f2<=b.f2

preserve table a  (because it is the one mentioned on the LEFT side of the
word JOIN; with    a RIGHT JOIN b   that would be table b that is
preserved).

BUT since the row  {a, 3}  has disappeared from the inner join, the LEFT
join re-introduced it:

a, 1, b, 1
a, 1, b, 2
a, 2, b, 2
a, 3, -, -

(where I used - in place of a NULL).

You can take on yourself to see than FROM a RIGHT JOIN b ON a.f2<=b.f2   is,
here, identical to the inner join, since no rows, in b, had completely
disappeared.

*****************
A FULL OUTER join, not directly supported by Jet, allows to preserved BOTH
tables.

*****************
An UNION join, not supported directly by Jet, neither by MS SQL Server,
produces something like:

a, 1, -, -
a, 2, -, -
a, 3, -, -
-, -, b, 1
-, -, b, 2

which, in MS SQL Server, can be accomplished supplying a FULL OUTER JOIN
with a ON clause that is always false.

*****************
Final remarks:

   the WHERE clause logically occurs always after the JOIN (logically) and
before any GROUP or aggregate.

   what is described, here up, is the intended result. The database engine
can take short-cuts, that is, it may proceed differently to reach the
result, and not use the technique we used to ILLUSTRATE the logical intended
result, as long as the result IS the same as if the long method (we used to
illustrate) had been used.

As example,

SELECT COUNT(*)
FROM a, b

which is a cross join between the two tables, so, you now know that the
result can be computed by a simple multiplication of the number of rows in a
by the number of rows in b, ... without making any join at all.

Hoping it may help,
Vanderghast, Access MVP