13

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

Roland
  • 7,525
  • 13
  • 61
  • 124
Prady
  • 10,978
  • 39
  • 124
  • 176

5 Answers5

17
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.

David Fells
  • 6,678
  • 1
  • 22
  • 34
  • I think you mean `LEFT OUTER JOIN`. – Oded Apr 30 '11 at 07:16
  • 4
    OUTER is a standard, but optional, keyword. – David Fells Apr 30 '11 at 07:22
  • Does adding outer join make any difference? – Prady Apr 30 '11 at 07:23
  • 4
    OUTER has become depracated in SQL Server since 2000. University professors still like it, but the SQL Server query optimiser means OUTER, INNER & RIGHT keywords are no longer needed at all. JOIN implies INNER and LEFT JOIN implies OUTER. – Gats Apr 30 '11 at 07:33
  • 1
    David, there is something wrong with the query.. i dont get the right result. If you are using the data as given by Nighil below. i get the result of one,two and four – Prady Apr 30 '11 at 07:47
13
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

Nighil
  • 4,099
  • 7
  • 30
  • 56
5
SELECT B.Accountid
FROM TableB AS B 
LEFT JOIN TableA AS A ON A.ID = B.Accountid 
WHERE A.ID IS NULL
Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
Kumaran NJ
  • 59
  • 1
  • 1
3

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

Andrei Sfat
  • 8,440
  • 5
  • 49
  • 69
Kuntady Nithesh
  • 11,371
  • 20
  • 63
  • 86
1

This will generate same results.

select * from TableB where Accountid not in (select ID from TableA)
sajid
  • 83
  • 8
  • generate same results with what. Please clarify as your answer is quite confusing without context – Simas Joneliunas Jan 31 '20 at 10:32
  • sorry for late reply. this will work as if you want to get only those records from `TableB` which not exist in `TableA`, according to `AccountId` (belongs to TableB) and `ID` (belogns to TableA) – sajid Feb 01 '20 at 09:35