Join types

  • Thread starter Thread starter Luke Bellamy
  • Start date Start date
L

Luke Bellamy

Hi - does Access 2K2 support the concept of "full outer joins".
I know SQL server does but Access queries spit out syntax errors
so I'm assuming it cannot be done.

Thankyou
 
Dear Luke:

Well, it CAN be done, but not in the standard way.

The simple explanation is to create a UNION of a LEFT JOIN and a RIGHT
JOIN.

For good performance, I prefer using a UNION ALL of the LEFT JOIN and
the RIGHT JOIN in which I remove those rows from the RIGHT JOIN that
would duplicate the rows in the LEFT JOIN.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks Tom, makes sense.
I understand that the UNION of the LEFT JOIN and RIGHT JOIN would cause
duplicates. You say you remove the rows from the RIGHT JOIN that was cause
duplicates
that exist in the LEFT JOIN below. How do you achieve this?

Thanks again
---------------
Luke Bellamy
Newcastle, Australia

Tom Ellison said:
Dear Luke:

Well, it CAN be done, but not in the standard way.

The simple explanation is to create a UNION of a LEFT JOIN and a RIGHT
JOIN.

For good performance, I prefer using a UNION ALL of the LEFT JOIN and
the RIGHT JOIN in which I remove those rows from the RIGHT JOIN that
would duplicate the rows in the LEFT JOIN.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Dear Luke:

To make sure everyone understands, the original recommendation is to
use UNION ALL to remove those duplicates. That's usually fine, but
there's extra processing needed to do this.

To understand the duplicating, first let's understand where it comes
from.

There are 3 cases:

- rows in set A that have related rows in set B (the ones that would
show up in an inner join)

- rows in A not in B

- rows in B not in A

The first of these cases are the ones that show up as duplicates in a
UNION, and would be removed in a UNION ALL.

To take advantage of the better performance of the UNION, you can
simply filter out these rows from ONE of the two joins.

Assuming the two tables are joined on a common column "id" then do
this:

SELECT * FROM A LEFT JOIN B ON B.id = A.id
UNION ALL
SELECT * FROM A RIGHT JOIN B ON B.id = A.id
WHERE A.id IS NULL

The last line filters out all those common lines, allowing you to use
UNION ALL instead and making it run faster (usually - the filter takes
a little time)

Does this make sense? If you have a significant amount of data, you
can make a comparison with:

SELECT * FROM A LEFT JOIN B ON B.id = A.id
UNION
SELECT * FROM A RIGHT JOIN B ON B.id = A.id

If anybody does this, please get back with your performance results.

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


Thanks Tom, makes sense.
I understand that the UNION of the LEFT JOIN and RIGHT JOIN would cause
duplicates. You say you remove the rows from the RIGHT JOIN that was cause
duplicates
that exist in the LEFT JOIN below. How do you achieve this?

Thanks again
Click to expand...
 
Tom - Thanks for your help. I've got my query :)

I think I misunderstand something though. I was under
the impression UNION would remove dulpicates (true union) and
UNION ALL would simply "append" 2 queries.
This would make UNION ALL faster since it does not need
to compare duplicate data in each sub-query being unioned.

So you could use UNION ALL to make your query faster
as long as you remove duplicates in your WHERE clause
like you mentioned (WHERE A.id IS NULL).
I have done mine this way and performance is acceptable.

Thanks again Tom
 
Dear Luke:

Using UNION ALL and eliminating the duplicates with the WHERE as I
suggested may well be faster. That's why I usually go for the more
complex approach, and avoid UNION. What you are saying here is just
what I was trying to show you.

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