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