Sql Query help to get non matching records from tw

问题:

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 which is present in TableB but not available in TableA. It would be wonderful someone could explain how you would approach this query.

Required record would be Opp3 of tableB

Thanks

Prady

回答1:

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

回答2:

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.

回答3:

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

回答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