Outer Join tends to Complex

  Left outer join retreives all rows in the table1 which is left table ;even if there is no match with table2.


 Right outer join retreives all rows in the table1 which is right table ;even if there is no match with table2.
 
Example
 
CREATE TABLE DEPT_MASTER
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);
CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

ALTER TABLE USER_DETAILS ADD ( CONSTRAINT FK_USER_DETAILS_DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT_MASTER (DEPT_ID));

Insert some data in both tables.

Old Fashion Left Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC  from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;
New  Fashioned  Left Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d left outer join dept_master dd on D.DEPT_ID=DD.DEPT_ID order by 1;

Old Fashion Right Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID   order by 1;

New  Fashioned  Right Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d right outer join dept_master dd on d.DEPT_ID=DD.DEPT_ID order by 1;

------------------------------------------------------------------

Here the scope of this article is not meant to describe this in detail , only to analyze in detail about the Old fashioned outer join it becomes complex and complicated , we can just have a look on it.

Left outer join can be written as below in 3 ways

a) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,left table and (+) outer notation in left to right table column

b) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,right table and (+) outer notation in left to left table column

c) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;-- left outer join (actual) ,left table and (+) outer notation in right to right table column

 Right outer join can be written as below in 3 ways
 

a) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID order by 1;-- right outer join (actual) right table and (+) outer notation in left to left table column

b) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

c) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

Normal Join


1) select d.user_id,DD.DEPT_DESC from user_details d  join dept_master dd on d.dept_id=dd.dept_id;

2) select d.user_id,DD.DEPT_DESC from user_details d natural join dept_master dd ;

3) select d.user_id,DD.DEPT_DESC from user_details d join dept_master dd using(DEPT_ID) ;

-------------