I have 2 tables, 1 is the result of a qry from months ago the 2nd is results of a current qry. I would like to find records in table1 that are not in table 2, and records in table 2 that are not in table 1.


i found an example :

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 LEFT JOIN Table2 ON Table1.Field1= Table2.Field1
WHERE (((table2.Field1) Is Null))
UNION
SELECT Table2.Field1, Table2.Field2, Table2.Field3
FROM Table2 LEFT JOIN Table1 ON Table1.Field1= Table2.Field1
WHERE (((table1.Field1) Is Null))

- but i am concerned that this wont do the trick -since there are lots of $ amounts and dates that will match and other colms also.

would i need a union qry like this and have to left join on EVERY COLUMN and have every column in the where clause?

Tnks

M~

If you are running Oracle, you might consider using the MINUS operator

Example:

select column_1, column_2, column_3, ...<include all columns>
from table1
MINUS
select column_1, column_2, column_3, ...<include all columns>
from table2

will return all rows in table1 that do not have a match in table2.

The $'s wont be a problem, you will only have to join/where on a candidate key for each table.

"I would like to find records in table1 that are not in table 2, and records in table 2 that are not in table 1."

(select name, dept
from table1
minus
select name, dept
from table2)
UNION
(select name, dept
from table2
minus
select name, dept
from table1);

I personally prefer the first query although I initially just did a left outer join, because it seems easier to follow and smaller to write.

alternatively if you like UNION, INTERSECT and MINUS you can use all three of them

(select *
from table1
UNION
select *
from table2)
MINUS
(select *
from table1
INTERSECT
select *
from table2);

I do like the outer join the best as it most probably would be faster, however these are in my opinion slower alternatives.