Retrieve non matching record using join

Question

I have two tables, table A and table B.

Both have 4 matching records and table A contains 6 records that do not match.

With the help of join how do I retrieve non matching records?

Answer 1

You can use a left outer join and test for B.ID is null. This sample will run in SQL Server 2008 but the query works in versions before that.

declare @TableA table (ID int)
declare @TableB table (ID int)

insert into @TableA values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
insert into @TableB values (1),(2),(3),(4)

select A.*
from @TableA as A
  left outer join @TableB as B
    on A.ID = B.ID
where B.ID is null

Result:

ID
--
5
6
7
8
9
10

Answer 2

Check this posts once

How to retrieve non-matching results in mysql

Returning non-matching records between 2 tables sql server