What is the MS Access SQL equivalent of FULL OUTER JOIN with a.key IS NULL and b.key IS NULL

Question

Example Query that I want to execute in MS Access SQL:

SELECT *
FROM TableA AS a
FULL OUTER JOIN TableB AS b
ON a.key = b.key
WHERE a.key IS NULL
OR b.key IS NULL

Since MS Access SQL does not allow FULL OUTER JOIN, I tried using the code below but the results aren't right.

SELECT *
FROM (TableA AS a
LEFT JOIN TableB AS b
ON a.key = b.key)
RIGHT JOIN TableB AS c
ON a.key = c.key
WHERE b.key IS NULL
OR a.key IS NULL

Does anyone know how to construct the MS Access SQL equivalent of the Example Query above that I'm trying to execute?

Answer 1

Use:

select . . . 
from a
where not exists (select 1 from b where b.key = a.key)
union all
select . . .
from b
where not exists (select 1 from a where a.key = b.key);

The . . . is for the columns that you want.

You could use * if you used:

select a.*, b.*
from a left join
     b
     on 1 = 0
where not exists (select 1 from b where b.key = a.key)
union all
select a.*, b.*
from b left join
     a
     on 1 = 0
where not exists (select 1 from a where a.key = b.key);