1/28/16

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





0 comments:

Post a Comment