HI,
Between the same two tables, all their join should be of the same type:
FROM a INNER JOIN b ON a.f1=b.f2 AND a.f3=b.f4
the LEFT, or RIGHT, or INNER, is applied to the whole relation. You cannot
have a.f1=b.f2 as a LEFT join and a.f3=b.f4 as a RIGHT join.
An inner join is like a cross join to which we apply a where clause:
FROM a INNER JOIN b ON a.f1=b.f2
is LOGICALLY the same as
FROM a, b
WHERE a.f1=b.f2
An outer join decides to keep all the records from ONE of the tables. That
table is called the PRESERVED table. If the logical equivalent WHERE would
have remove ALL occurrence of a given row of the preserved table, that row
is logically re-introduced, and the UN-PRESERVED table selected fields are
set to NULLs. The preserved table is the table that occurs at the RIGHT, if
you use RIGHT, or the one at the LEFT, if you use LEFT, of the word JOIN, in
the SQL TEXT.
There are two other types of outer joins not supported in Jet. There is the
full outer join and the union join. The full outer join is logically
equivalent to declare each table as "preserved" (each, but separately, in
turn). A union join is like a full outer join on a condition that is always
false, leaving, as a picture, the result:
Table1.*, NULLs
NULLS, Table2.*
Finally, Jet does not obey what I described previously, for outer joins, if
part of the ON clause do not imply the two tables.
FROM a LEFT JOIN b ON a.f1=b.f2 AND a.f3=4444
would be executed as
FROM a LEFT JOIN b ON a.f1=b.f2
WHERE a.f3=4444
If you have followed up to now, you can see that those two statements are
not logically equivalent. The first statement keeps all records from table
a, EVEN those with a.f2 <> 4444! The second statement keep only those
where a.f3=4444.
In Northwind, with Jet,
SELECT Customers.City
FROM Customers LEFT JOIN Orders ON (Customers.CustomerID = Orders.CustomerID
AND Customers.City="Iqaluit")
that returns NOTHING... while an outer join should PRESERVE one table! In MS
SQL Server, the same query will return ALL the records from Customers, even
those not living in Iqaluit.
Hoping it may help,
Vanderghast, Access MVP
