SQL Full Join

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to find out if SQL in access allows a full join. I can get the
left and right to work but not the full. If it is possible please help with
the syntax. Thanks.
 
Hi,


Here are two other alternatives:


SELECT x.id FROM x
UNION
SELECT y.id FROM y

saved as qu1. Then


SELECT x.*, y.*
FROM ( qu1 LEFT JOIN x ON qu1.id=x.id)
LEFT JOIN y ON qu1.id=y.id

will do your full outer join (on id).


Another solution is to use the union after the outer join:


SELECT x.id, y.id
FROM x LEFT JOIN y ON x.id=y.id

UNION

SELECT x.id, y.id
FROM x RIGHT JOIN y ON x.id=y.id
WHERE x.id IS NULL


You recognize the right join as finding records in x not in y, which
complete the first left outer join. Indeed, you have to NOT include those
already included by the first LEFT JOIN and an UNION ALL won't do the trick
since it is possible that the initial tables both have duplicated record (a
UNION ALL would incorrectly remove those dup). So, the WHERE x.id IS NULL is
really important, in general.





Hoping it may help,
Vanderghast, Access MVP
 
Back
Top