Oracle SQL joins

The purpose of a SQL join is to combine the data across tables.

A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.


SQL Join Types 

Inner Join	This will display all the records that have matched.
Left Outer	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.
Right Outer	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.
Full Outer	This will display the all matching records and the non-matching records from both tables.

  

Assume that we have the following tables.

SQL> select * from Dept;

DEPTNO	DNAME	LOC
10	Mkt	Hyd
20	Fin	Bang
30	Hr	Bombay

SQL> select * from Emp;

EMPNO	ENAME	JOB	MGR	DEPTNO
111	saketh	analyst	444	10
222	sudha	clerk	333	20
333	jagan	manager	111	10
444	madhu	engineer	222	40

INNER JOIN
 
This will display all the records that have matched. 

 Example:     
 

SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC

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

       111          saketh     analyst      mkt             hyd

       333          jagan       manager   mkt            hyd

       222          sudha      clerk          fin             bang

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.

Example: 

 

SQL>Select empno,ename,job,dname,loc from emp e left 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(+);

   

     EMPNO     ENAME   JOB       DNAME      LOC

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

       111          saketh    analyst       mkt        hyd

       333          jagan      manager    mkt        hyd

       222          sudha     clerk           fin          bang

       444          madhu    engineer

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.

Example:

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;
 

     EMPNO    ENAME     JOB      DNAME      LOC

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

       111          saketh     analyst      mkt        hyd

       333          jagan       manager   mkt        hyd

       222          sudha      clerk          fin          bang

                                                       hr           bombay

FULL OUTER JOIN

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

Example:  

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

 EMPNO   ENAME    JOB        DNAME      LOC

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

       333     jagan     manager    mkt        hyd

       111     saketh   analyst       mkt        hyd

       222     sudha    clerk           fin        bang

       444     madhu   engineer     
                                                 hr         bombay