Monday, October 1, 2012

FULL OUTER JOIN into the SQL Server query


Full outer join is use full when we need following kind of result from tow individual queries

Result 1

IdColumn Data1
1 Hello1
2 Hello2
. .
. .
10 Hello10

Result 2

IdColumn Data2
1 Hi1
2 Hi2
. .
. .
10 Hi10

Final Result = Result 1 + Result 2

IdColumn Data1 Data2
1 Hello1 Hi1
2 Hello2 Hi2
. . .
. . .
10 Hello10 Hi10


{{ For Example }}

select isnull(A.Id,B.Id) as Id , A.*, B.*
from
( select IdColumn as Id,* from xyz ) A
full outer join
( select IdColumn as Id,* from abc ) B
on A.Id = B.Id

{{ NOTE }}
You have to care about null in the result set when there are no data in either table for the respected IDs.