Thursday, December 11, 2008

Left outer join in oracle

Left outer join, which when we try to apply to join 2 tables say tbl1 and tbl2.
(Tbl1 <------ Left Join --------Tbl2)
It does mean to join all rows available in tbl1 (left side table) to rows in tbl2.
Rows from tbl2 are available in result set depending upon the joint condition where as for tbl1 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 :
Tbl1 Data




Tbl2 Data




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

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





We can observe there are 5 rows in result set with ID1 as all values from tbl1 , where as there is no row with ID2 as 6 , since there is no corresponding row in tbl1 with ID=6.


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
left 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: