Full Outer Joins

[arvindn]

How do i write a query for full outer join in MS-Access ?

(Right now i am using a union of left join from table t1 to t2 and remaining values of t2 to solve it but it is slow and inelegant)

 

[EMP]

Try a union query of a left join with a right join of the tables e.g.

SELECT *
FROM t1 LEFT JOIN t2 ON t1.ID = t2.ID
UNION
SELECT *
FROM t1 RIGHT JOIN t2 ON t1.ID = t2.ID;

 

[arvindn]

Try a union query of a left join with a right join of the tables e.g.

SELECT *
FROM t1 LEFT JOIN t2 ON t1.ID = t2.ID
UNION
SELECT *
FROM t1 RIGHT JOIN t2 ON t1.ID = t2.ID;

If u think carefully this will fetch duplicates of records present in inner-join.
Also, if u observe as i said in the original post that i am currently using

SELECT *
FROM t1 LEFT JOIN t2 ON t1.ID = t2.ID
UNION
SELECT *
FROM t1 RIGHT JOIN t2 ON t1.ID = t2.ID where t1.id is null

which is the correct solution but is slow. I want to do away with the union part of it if possible.
Thanks for posting.

 

[EMP]

If u think carefully this will fetch duplicates of records present in inner-join

Are you sure it will fetch duplicates of records? Have you tried it?

 

[arvindn]

It is very obvious that it will do so.

Apart from that the point is i wish to get rid of unions as they will be clumbersome to work with and slow if full outer join of more than two tables is reqd.

 

[EMP]

It is very obvious that it will do so.

Obvious?

The following is taken from the Access help file:-

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned.

 

[arvindn]

Well i am genuinely sorry i messed up UNION and UNION ALL.
Thanks for bothering to correct me.

Can we do away with the UNION part altogether coz as i said i already had a slightly different solution that used UNION.

As far as i know
select * from t1 full outer join t2 on t1.id=t2.id (with trivial variations) is a part of standard SQL but is not accepted by MS Access. I wanted to know whether i am missing something in the syntax or is there a cleaner way?

 

[arvindn]

Thanks a lot Pat. That solved my query.