Outer Joins In Access

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

I have two tables that I would like to join together.
The problem is I would like to see all records from both
tables whether they have a corresponding record in the
other table or not. In SQL Server I can use the Full
Outer Join command to do this. Is there a way to do this
in Access?

Thanks,
Chuck
 
Dear Chuck:

Since SQL Server MSDE is a part of Access, I must assume that when you
say Access you are referring specifically to that part of Access that
uses the Jet database engine.

A FULL OUTER JOIN can be accomplished with a UNION of a LEFT JOIN and
a RIGHT JOIN. However, I consider this to be only part of the way it
should be done.

If you use a UNION, it will remove rows that would otherwise be
duplicated between the LEFT JOIN and the RIGHT JOIN, those rows being
the ones you would get with an INNER JOIN. However, removing these
rows takes time.

For good performance, I usually perform the LEFT JOIN and then UNION
ALL to the RIGHT JOIN, but I filter out those rows in the RIGHT JOIN
where the left side IS NOT NULL. This does the job of eliminating
those duplicated by preventing them from even appearing initially. My
theory is that this is a much faster way to accomplish the task.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top