Wednesday, December 10, 2008

Full outer join in oracle

Full outer join is opposite to inner join.
Please refer inner join at
Inner join in prog. findings
To explain inner join in brief consider the below example,
Ex:-
There are 2 tables in oracle, named tbl1 and tbl2 with data as
Tbl1 Data




Tbl2 Data




If we inner join the above 2 tables placing condition tbl1.id=tbl2.id, then the rows from tbl1 and tbl2 are considered whose ids are in the set of ({1, 2, 3, 4, null} intersect {1, 2, 6} = {1, 2})
So we get only 2 rows after inner joining above two tables.

Query:-

SELECT TBL1.id AS id1,TBL1.name AS name1,TBL2.id AS id2 , TBL2.name AS name2 FROM TBL1 inner join TBL2 ON TBL1.id=TBL2.id

Result:-




Where as in full outer join, using same joint condition tbl1.id=tbl2.id then

The rows from tbl1 and tbl2 are considered whose ids are in the set of ({1, 2, 3, 4, null} Union {1, 2, 6} = {1, 2, 3, 4, null, 6})
Query:-
SELECT TBL1.id AS id1,TBL1.name AS name1,TBL2.id AS id2 , TBL2.name AS name2 FROM TBL1 full outer join TBL2 ON TBL1.id=TBL2.id


Result:-



Note: -
1) If there does not exit row with the condition specific in either of two tables the values are null.
Since a row with ID=6 is not there in tbl1, so we can observe the row in result set with ID2=6 has ID1=null, Name1=null.

2) I have inserted one more row in tbl2 with ID=1 which is already there in tbl2.

Data:-




Query:-
SELECT TBL1.id AS id1,TBL1.name AS name1,TBL2.id AS id2 , TBL2.name AS name2 FROM TBL1 full outer join TBL2 ON TBL1.id=TBL2.id


Result:-



We can observe that since there is only row in tbl1 with ID=1, but 2 rows in tbl2 with ID=1.
we can see 2 rows in result set after running full outer join query with ID2=1 and 2 rows having same Name1 but different Name2.

Similarly for Inner join,



No comments: