JOINS

The purpose of a 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.

TYPES

Equi join

Non-equi join

Self join

Natural join

Cross join

Outer join

Ø  Left outer

Ø  Right outer

Ø  Full outer

Inner join

Using clause

On clause

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

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex:

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

USING CLAUSE

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

EMPNO      ENAME       JOB   DNAME        LOC

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

111	saketh	analyst	mkt	hyd
333	jagan	manager mkt		hyd
222	sudha	clerk	fin	bang

ON CLAUSE

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

EMPNO      ENAME       JOB   DNAME        LOC

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

111	saketh	analyst   mkt	hyd
333	jagan	manager mkt	hyd

222           sudha       clerk        fin         bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex:

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

EMPNO	ENAME	JOBDNAME		LOC
---------- ---------- ---------- ---------- ----------
222	sudha	clerk	mkt	hyd
444	madhu	engineer	mkt	hyd
444	madhu	engineer	fin	bang
444	madhu	engineer	hr	bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr;

EMPNO   ENAME   JOB          DEPTNO

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

111         jagan       analyst          10

222         madhu       clerk            40

333         sudha       manager      20

444         saketh   engineer         10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

SQL> select empno,ename,job,dname,loc from emp natural join dept;

EMPNO  ENAME        JOB      DNAME   LOC

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

111	saketh	analyst	mkt	hyd
333	jagan	manager	mkt	hyd
222	sudha	clerk	fin	bang

CROSS JOIN

This will gives the cross product.

Ex:

SQL> select empno,ename,job,dname,loc from emp cross join dept;

EMPNO ENAME   JOB            DNAME       LOC

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

111	saketh	analyst	mkt	hyd
222	sudha	clerk	mkt	hyd
333	jagan	manager	mkt	hyd
444	madhu	engineer	mkt	hyd
111	saketh	analyst	fin	bang
222	sudha	clerk	fin	bang
333	jagan	manager	fin	bang
444	madhu	engineer	fin	bang
111	saketh	analyst	hr	bombay
222	sudha	clerk	hr	bombay
333	jagan	manager	hr	bombay
444	madhu	engineer	hr	bombay

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.

Ex:

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.

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;

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.

Ex:

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

INNER JOIN

This will display all the records that have matched.

Ex:

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