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
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 |