Sql Query help to get non matching records from two tables

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

---

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.

---

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

---

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

---

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

---

This will generate same results.

select * from TableB where Accountid not in (select ID from TableA)