Monday, May 30, 2016

How to get unmatched rows from two tables

Let Create  two tables

create table t1 (Id int, name varchar(50))
create table t2 (Id int, name varchar(50))

insert t1 values (1,       'avi')
insert t1 values (2,      'ravi')
insert t1 values (3,       'sun')
insert t1 values (5,       'naveen')
insert t1 values (7,       'rachin')

insert t2 values (1,       'avi')
insert t2 values (2,       'ravi')
insert t2 values (4,       'naveen')
insert t2 values (6,       'raja')


Query to find unmatched rows 

Option 1:
Select Id, Name
from T1
where Id not in (select Id from T2)
UNION
Select Id, Name
from t2

where Id not in (select Id from t1)

Option 2:

select isnull(t1.id, t2.id), isnull(t1.name,t2.name)  from t1
full outer join t2 on t1.id = t2.id
where t2.Id is null or t1.id is null



No comments:

Post a Comment