Oracle SQL Joins

1) Equi-Joins
2) Non Equi-Joins
3) Inner Join
4) Self Join
5) Outer Join
    1- Left Outer Join
    2- Right Outer Join
    3- Full Outer Join




1) Equi Join
Here we Join Table using " = " symbol.

Example : 

Below we have employee table


EMPNOENAMEJOBMGRHIREDATESALARYCOMMISIONDEPTNO
7369SMITHCLERK790217-Dec-80800 20
7499ALLENSALESMAN769820-Feb-81160030030
7521WARDSALESMAN769822-Feb-81125050030
7566JONESMANAGER783902-Apr-812975 20
7654MARTINSALESMAN769828-Sep-811250140030
7698BLAKEMANAGER783901-May-812850 30
7782CLARKMANAGER783909-Jun-812450 10
7788SCOTTANALYST756619-Apr-873000 20
7839KINGPRESIDENT 17-Nov-815000 10
7844TURNERSALESMAN769808-Sep-811500030
7876ADAMSCLERK778823-May-871100 20
7900JAMESCLERK769803-Dec-81950 30
7902FORDANALYST756603-Dec-813000 20
7934MILLERCLERK778223-Jan-821300 10


Department Table

 
DEPTNO  DNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

Below is Equi-Join Query


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

It will give output as below

EMPNOENAMEJOBDEPTNODNAMELOC
7782CLARKMANAGER10ACCOUNTINGNEW YORK
7839KINGPRESIDENT10ACCOUNTINGNEW YORK
7934MILLERCLERK10ACCOUNTINGNEW YORK
7566JONESMANAGER20RESEARCHDALLAS
7902FORDANALYST20RESEARCHDALLAS
7876ADAMSCLERK20RESEARCHDALLAS
7369SMITHCLERK20RESEARCHDALLAS
7788SCOTTANALYST20RESEARCHDALLAS
7521WARDSALESMAN30SALESCHICAGO
7844TURNERSALESMAN30SALESCHICAGO
7499ALLENSALESMAN30SALESCHICAGO
7900JAMESCLERK30SALESCHICAGO
7698BLAKEMANAGER30SALESCHICAGO
7654MARTINSALESMAN30SALESCHICAGO



2) Non-Equi Joins

It simply means we wil not use " = " symbol, we can use " <> , > , < ," etc

Below is grades table, Now we will do Non-Equi Join between employee table and grade table

GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999



select e.empno,e.ename,e.job,g.grade
from emp e,
     salgrade g
where e.sal between g.losal and g.hisal;


3) Inner Join
Almost same as equi join, but here we are free to user operators other than "="

select e.empno,e.ename,e.job
from emp e inner join dept d
on e.deptno <> deptno;

-Output is useless info, but in some cases could be used.

4) Self Join.

Joining of a table with itself, in the background oracle creates two copies of the table


select e.ename subordinate,
       m.ename supervisor
from emp e,
     emp m
where e.mgr=m.empno order by e.rowid;

Will give output as below

SubordinateSupervisor
SMITHFORD
ALLENBLAKE
WARDBLAKE
JONESKING
MARTINBLAKE
BLAKEKING
CLARKKING
SCOTTJONES
TURNERBLAKE
ADAMSSCOTT
JAMESBLAKE
FORDJONES
MILLERCLARK


5) Outer Join
    - Left Outer Join
    - Right Outer Join
    - Full Outer Join

We will perform operation on below two tables

Table 1 emp1
empno ename deptno
101 rohan sales
102 sohan sales
103 mohan sales
104 zohan sales
105 ram marketing
106 rahim marketing

Table 2 emp2

empno ename deptno
101 karl hr
102 voltaire hr
103 socrates hr
108 emerson hr
109 mao hr
110 lenin hr
111 marx hr

Left outer join

select o.empno,o.ename,s.empno,s.ename
from emp1 o left outer join emp2 s
on o.EMPNO = s.EMPNO;

could also be written as 

select o.empno,o.ename,s.empno,s.ename
from emp1 o,emp2 s
where o.empno=s.empno(+);

Literal sense, it returns all the unique data of emp1 and matching data of emp1 and emp2

empno ename empno_1 ename_1
101 rohan 101 karl
102 sohan 102 voltaire
103 mohan 103 socrates
106 rahim null null
105 ram null null
104 zohan null null

Right Outer Join


select o.empno,o.ename,s.empno,s.ename
from emp1 o right outer join emp2 s
on o.empno=s.empno;

could also be written as

select o.empno,o.ename,s.empno,s.ename
from emp1 o,emp2 s
where o.empno(+)=s.empno;

Output as below

empno ename empno_1 ename_1
101 rohan 101 karl
102 sohan 102 voltaire
103 mohan 103 socrates
null null 111 marx
null null 109 mao
null null 110 lenin
null null 108 emerson

Literal sense, give all unique values of emp2 and all matching entries of emp1 and emp2


--Full Outer Join

empno ename empno_1 ename_1
101 rohan 101 karl
102 sohan 102 voltaire
103 mohan 103 socrates
null null 108 emerson
null null 109 mao
null null 110 lenin
null null 111 marx
106 rahim null null
105 ram null null
104 zohan null null

Gives us all the combination



Comments