Outer Join

Outer join gives the non-matching records along with matching records.

Left Outer Join

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex1:    SQL> select empno,ename,job,dname,loc from emp e left outer join dept d   on(e.deptno=d.deptno);

Ex2:    SQL> select empno,ename,job,dname,loc from emp e,dept d where 

             e.deptno=d.deptno(+);

Right Outer Join

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex: SQL> select empno,ename,job,dname,loc from emp e right outer join dept d        on(e.deptno=d.deptno);

Or

         SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =    d.deptno;

Full Outer Join

This will display the all matching records and the non-matching records from both tables.

Ex: SQL> select empno,ename,job,dname,loc from emp e full outer join dept d     on(e.deptno=d.deptno);

                                    OR

SQL> select p.part_id, s.supplier_name

             from part p, supplier s

             where p.supplier_id = s.supplier_id (+)

             union

             select p.part_id, s.supplier_name

             from part p, supplier s

             where p.supplier_id (+) = s.supplier_id;