Outer-Join

Outer join returns all records from one table and those records  from another table where meets joining condition.
There are three types of outer joins
=>Left Outer-join
=>Right outer-join
=>Full outer-join

Left Outer-join
 In Left Outer-Join  retrieves the unmatched rows  and matched from the left (1st)table and only matched rows from right(2nd) table.

The result of a left outer join for table A and B always contains all records of the Left table (A), even if the condition does not match with  record in the Right table (B).  This means that a left outer join returns all the values from the left table, plus matched values from the Right table or returns NULL in case of no matching join condition  satisfied.


Example Select an employee's department where they have not been assigned to a department

	

	SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

	

or

	SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+);
	

Right Outer-join
 

In Right Outer-Join  retrieves the unmatched rows  and matched from the Right table and only matched rows from Left table.

The result of a Right outer join for table A and B always contains all records of the Right table (B), even if the condition does not match with  record in the Left table (A).  This means that a Right outer join returns all the values from the Right table, plus matched values from the Left table or returns NULL in case of no matching join condition  satisfied.


Example List dept no., Dept name for all the departments in which there are no employees in the department.

	SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
	 

OR

	SELECT * FROM EMP,DEPT EMP.DEPTNO(+)=DEPT.DEPTNO

Full Outer-join

Full Outer-join is combination of left and right outer join.It returns both match and unmatched row from both tables.

Example Retrieve each employee who is in a department and each department that has an employee and also  each employee who is not part of a department and each department which doesn't have an employee.

	SELECT * FROM EMP FULL OUTER JOIN  DEPT ON EMP.DEPTNO=DEPT.DEPTNO
	  

OR

	SELECT * FROM EMP,DEPT EMP.DEPTNO(+)=DEPT.DEPTNO(+)