Finding not matching records in two tables

[mrfobking]

can someone help me with performing the below task? I have two tables, which can be linked with a 'ID' column. I would like to create a query that gets me the records that do NOT match.

I can find the matching records by joining the two tables, but do not know how to find the non-matching records.

Thanks in advance for your help.

 

[xenou]

Typically you perform a Left Join and filter for the NULL matches on the joined table. I think you can also use a COUNT() strategy which might be how the find unmatched records wizard works.

If you want to find non matches in both tables you have to run it twice - first the unmatched IDs in Table2 vis a vis Table1, then vice versa. You can UNION the results to form a single list of unmatched IDs (this doesn't tell you which table they are in/not in though - but you could add a Table Name as a second field.

SELECT 
	ID 
FROM 
	Table1 
	LEFT JOIN 
	Table2 
	ON Table1.ID = Table2.ID 
WHERE
	Table2.ID IS NULL
	
UNION

SELECT 
	Table2.ID 
FROM 
	Table2 
	LEFT JOIN 
	Table1 
	ON Table2.ID = Table1.ID 
WHERE
	Table1.ID IS NULL