1

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?

Kristin Wong
  • 13
  • 3

1 Answers1

0

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This works! Thanks so much! But I'm wondering, when would I use one over the other? Is it just preference or is there a performance difference or something else that I'm not aware of? Thanks again! – Kristin Wong Apr 06 '20 at 03:03
  • @KristinWong . . . Use the first form if you are comfortable listing the columns. The second is just a way for you to use `*` so you don't have to list the column names. – Gordon Linoff Apr 06 '20 at 10:24