Repeating records mystery

  • Thread starter Thread starter Razor
  • Start date Start date
R

Razor

Hi,

I have a table with 30,000 records.

When I do an INNER JOIN with a second table on
ExpenseCode, the records returned by the query actually
GROW to 34,000.

How is this possible? What records are being repeated?

Expense code is unique to the second table and repeats in
the 30,000-record table.

Thanks!

Regards,
Razor
 
John,
Can I extend this to mean that if a value exists "n" times
in the second table, Access will create "n" rows with that
value in the results?
Cheers,
Razor
 
Yes, If you are joining on that field. Access won't know which of the several
values is "the one" and will create "n" rows.

If you want to see this taken to an extreme, put both tables in a query without
a join. You will get one row in the query for every combination of the rows in
the two tables - it's called a cartesian join. That type of join is actually
useful in some conditions.
 
John,
Does this phenomenon apply to inner joins ONLY or to
right and left outer joins as well?
Razor
 
This behavior applies to all types of joins. If you want to study it, make a
couple of small tables and experiment. If you do this yourself, you will
probably (1) remember the answers more easily, (2) get quicker answers, and (3)
have a useful technique for studying the inner workings of queries.
 
Hi,


Indeed. You (logically) start with a Cartesian join. For an inner join, you evaluate the ON clause
on each resulting row and only keep the ones where the ON evaluates to TRUE.

The order of the rows is not relevant in the result.

The JOIN do not have to proceed the way I described it, but the result we obtain should be the
same. The database engine "know-how" can proceed differently, as long as the result is the same.

For a LEFT join, if, for a given record from the LEFT table, the ON clause evaluates to FALSE, or
to NULL, for all the generated records in the result, we re-inject the said record from the LEFT
table, and supply NULL for the unpreserved table, the right table.

Similar LOGICAL procedure for a RIGHT join.

Note that Jet behaves differently. If the ON clause segment involves only one table, like

myTable.MyField = 22

Jet moves the segment from the ON to the WHERE clause. That is not standard.


The JOIN is always evaluated BEFORE the WHERE clause. That makes JET behaviour more "natural", in
some situations, but not "standard", when conditions involving only one table (or no table at all)
are involved in a ON clause.


If the ON clause implies something else that = in the comparisons, it is said to be a NON-EQUI
join. A non-equi join is not graphically "representable" in the Access Query Designer, but can be
evaluated noneless.


Vanderghast, Access MVP
 
Back
Top