Sql Query help to get non matching records from two tables

Question

I am trying to get non matching records from 2 tables

For ex

TableA
 ID           Account
 1               Acc1
 2               Acc2
 3               Acc3

 TableB
 Opp          Accountid
 Opp1            1
 Opp2            2
 Opp3            4

I need to know which accountid is present in TableB but not in TableA. It would be wonderful if someone could provide this query.

Required record would be Opp3 of tableB

Thanks

Prady

Answer 1

SELECT B.Accountid 
  FROM TableB AS B 
  LEFT 
  JOIN TableA AS A 
    ON A.ID = B.Accountid 
 WHERE A.ID IS NULL;

LEFT JOIN means it takes all the rows from the first table - if there are no matches on the first join condition, the result table columns for table B will be null - that's why it works.

Answer 2

create table #one (id int,acc nvarchar(25))
insert into #one (id , acc) values(1,'one') 
insert into #one (id , acc) values(2,'two') 
insert into #one (id , acc) values(3,'three') 

create table #two (acct nvarchar(25),ids int)
insert into #two (acct,ids) values('one',1) 
insert into #two (acct,ids) values('two',3) 
insert into #two (acct,ids) values('four',4) 

select ids from #two EXCEPT select id from #one 

drop table #one 
drop table #two 

test this one

Answer 3

SELECT B.Accountid
FROM TableB AS B 
LEFT JOIN TableA AS A ON A.ID = B.Accountid 
WHERE A.ID IS NULL

Answer 4

try this

(select * from t1
except 
select * from t2)

union

(select * from t2
except 
select * from t1)

thinking that you have the same number of columns in both tables

query mentioned above select ids from #two EXCEPT select id from #one will give u non matching rows from only #two . it will neglect that of #one