Wednesday, November 25, 2015

Unmatched Records from Two tables

Unmatched Records from Two tables:
create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

Select * from tab1
Select * from tab2
table 1:                                                   table 2:
                                               




Required Result:



Query:
select * from tab1 full outer join tab2 on tab1.id=tab2.id
where tab1.id not in(
                                 select a.id from tab1 as a join tab2 as b on                                           a.id=b.id
                                 ) or 

tab2.id not in (select b.id from tab1 as a join tab2 as b on a.id=b.id)

But if we want results like following:


Query:

Select IsNull(tab1.id,tab2.id),name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
where tab1.id not in(
                                  select a.id from tab1 as a join tab2 as b on                          a.id=b.id ) or 

tab2.id not in (select b.id from tab1 as a join tab2 as b on a.id=b.id)



No comments:

Post a Comment