Access support Left (outer) and Right (Outer) joins. It does not support
FULL Outer joins. You can simulate a Full Outer Join. Here is an excerpt
from an earlier posting by Michel Walsh
To emulate a Full Outer Join, you need to union a Left outer join with a
"frustrated" Right outer
join:
SELECT somefields
FROM tb1 LEFT JOIN tb2 ON tb1.f1 = tb2.fA
UNION
SELECT somefields
FROM tb1 RIGHT JOIN tb2 ON tb1.f1 = tb2.fA
WHERE tb1.f1 IS NULL
============================================================================
============================================================================
Michel Walsh example.
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.
