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


Microsoft t-SQL

Department Table

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

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

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

5) Outer Join

- Left Outer Join

- Right Outer Join

- Full Outer Join

We will perform operation on below two tables

Table 1 emp1

Table 2 emp2

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

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

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

--Full Outer Join

Gives us all the combination