Search This Blog

Saturday, May 26, 2012

query for full outer join with no repated value or column

Select * from R1 r1 full outer join R2 r2 on r1.NameId=r2.NameId
Select * from R1 r1 full outer join R2 r2 on r1.NameId=r2.NameId where r2.NameId is null or r1.NameId is null
Select * from R1 r1 full outer join R2 r2 on r1.NameId=r2.NameId where r1.NameId is null

Select r1.NameId, r1.FirstName,r1.LastName from R1 r1 full outer join R2 r2 on r1.NameId=r2.NameId where r2.NameId is null
union
Select r2.NameId,r2.FirstName,r2.LastName from R1 r1 full outer join R2 r2 on r1.NameId=r2.NameId where r1.NameId is null

table r1:-
NameId    FirstName    LastName
1    yogesh               pd
2    mohit              ranjan

table r2
NameId    FirstName    LastName
2    ashish        dsf
3    mohit        sdfdfs

result need:--
NameId    FirstName    LastName
1    yogesh        pd
3    mohit        sdfdfs

No comments :