Friday, December 12, 2008

Right outer join in oracle

Right outer join, which when we try to apply to join 2 tables say tbl1 and tbl2.
(Tbl1 ------ Right Join ------>Tbl2)
It does mean to join all rows available in tbl2 (right side table) to rows in tbl1.
Rows from tbl1 are available in result set depending upon the joint condition where as for tbl2 all rows are considered and can be restricted in joint condition or where clause.
Ex:-
There are 2 tables in oracle, named as tbl1 and tbl2 with data as
Tbl1 Data




Tbl2 Data



If we say right outer join for the above 2 tables placing condition tbl1.id=tbl2.id, then all rows from tbl2 are considered but from tbl1 whose ids are in the set of
({1, 2, 6} (all id’s available in tbl2, as we said right outer join)

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





We can observe there are 3 rows in result set with ID2 as all values from tbl2 , where as there is no row with ID1 as 3 or 4 , since there is no corresponding row in tbl2 with ID= 3 or 4.


Note: -
1) If there does not exit row with the condition specific in either of two tables the values are 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
right outer join TBL2 ON TBL1.id=TBL2.id

Result:-





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

No comments: