Query that gives me all matches and non matches

[John J.]

I have data like:
Table1 Table2
Fld1 Fld1
A A
B C
E D
F F

I would like to make a query that gives me

ResultTable with 2 fields:
InTbl1 InTbl2
A A
B
C
D
E
F F

Is this possible to do in one query?
Thank you.
John

 

[Allen Browne]

You will need to use 2 queries.

This gives you each unique value in either table:
SELECT F1 FROM Table1
UNION SELECT F1 FROM Table2;

Now outer-join your origional tables to this query to get the result you
wanted:
SELECT Table1.F1, Table2.F1
FROM (Query1 LEFT JOIN Table1 ON Query1.F1 = Table1.F1)
LEFT JOIN Table2 ON Query1.F1 = Table2.F1;

It may be possible to do that with a subquery, but I think it would be more
stable and efficient as a 2-step process.

 

[Paul Shapiro]

The operator you're looking for is Full Outer Join, which does exactly what
you asked for. SQL Server supports this operator, but Access does not. You
could write it as a union of 2 subqueries (untested syntax):

Select T1.Fld1 as InTbl1, T2.Fld1 as InTbl2
From Table1 as T1 Left Join Table2 as T2 On T1.Fld1=T2.Fld1

Union All

Select Null, T2.Fld1
From Table2 as T2
Where Not Exists (Select * From Table1 as T1 Where T1.Fld1=T2.Fld1)

 

[John J.]

Excellent. Thanks!

You will need to use 2 queries.

This gives you each unique value in either table:
SELECT F1 FROM Table1
UNION SELECT F1 FROM Table2;

Now outer-join your origional tables to this query to get the result you
wanted:
SELECT Table1.F1, Table2.F1
FROM (Query1 LEFT JOIN Table1 ON Query1.F1 = Table1.F1)
LEFT JOIN Table2 ON Query1.F1 = Table2.F1;

It may be possible to do that with a subquery, but I think it would be
more stable and efficient as a 2-step process.

 

[John J.]

Thanks! Works as well.

The operator you're looking for is Full Outer Join, which does exactly
what you asked for. SQL Server supports this operator, but Access does
not. You could write it as a union of 2 subqueries (untested syntax):

Select T1.Fld1 as InTbl1, T2.Fld1 as InTbl2
From Table1 as T1 Left Join Table2 as T2 On T1.Fld1=T2.Fld1

Union All

Select Null, T2.Fld1
From Table2 as T2
Where Not Exists (Select * From Table1 as T1 Where T1.Fld1=T2.Fld1)