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)
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