Categories of Joins

Oracle Proprietary Joins (8i and prior):
Ø  Equi Join
Ø  Non-Equi Join
Ø  Outer Join
Ø  Self Join
ANSI SQL: 1999 Compliant Joins
Ø  Cross Joins
Ø  Natural Joins
Ø  Using Clause
Ø  Full OR Two Sided Outer Joins
Ø  Arbitrary join condition for outer joins
ISO or ANSI Joins:
Cross Join:
Ex:
  • Select Ename, Dept.Deptno, Dname, Loc From Emp CROSS JOIN Dept where Emp.Deptno = Dept.Deptno;
Natural Join:
Ex:
  • Select Ename, Deptno, Dname, Loc From Emp Natural join Dept;
USING Clause:
Ex:
  • Select Ename, Deptno, Dname, Loc From Emp Join Dept Using(deptno);
INNER JOIN:
Ex:
  • Select Ename, Dept.Deptno, Dname, Loc From Emp JOIN Dept ON Emp.Deptno = Dept.Deptno;
Self Join:
  • Select E.Ename Employee, M.Ename Manager From Emp E INNER JOIN Emp M On (E.MGR = M.Empno);
Join on More Than Two Tables:
Ex:
  • Select Ename, Sal, Grade, Dept.Deptno, Dname From Emp JOIN Dept ON Emp.Deptno = Dept.Deptno JOIN SalGrade On Emp.Sal Between Losal and Hisal;
  • Select E.Ename, M.Ename, E.Sal, Grade, D.Deptno, Dname From Emp E INNER JOIN Dept D on E.Deptno = D.Deptno INNER JOIN Emp M on E.Empno = M.MGR INNER JOIN SalGrade S On E.Sal Between Losal and Hisal;
Right Outer Join:
Ex:
  • Select Ename, Dept.Deptno, Dname, Loc From Emp RIGHT JOIN Dept ON Emp.Deptno = Dept.Deptno;
Left Outer Join:
Ex:
  • Select Ename, Dept.Deptno, Dname, Loc From Emp LEFT JOIN Dept ON Emp.Deptno = Dept.Deptno;
FULL Join:
Ex:
  • Select Ename, Dept.Deptno, Dname, Loc From Emp FULL JOIN Dept On Dept.Deptno = Emp.Deptno;
  • Select Ename, Dept.Deptno, Dname, Loc From Emp FULL JOIN Dept on Emp.Deptno = Dept.Deptno;
Some Complications:
Ex:

  • Select E.Ename Employee, M.Ename Manager From Emp E LEFT OUTER JOIN Emp M ON (E.MGR = M.Empno) Order by 2;
  • Select E.Ename, M.Ename, E.Sal, Grade, D.Deptno, Dname From Emp E INNER JOIN Dept D ON E.Deptno = D.Deptno INNER JOIN Emp M ON E.Empno = M.MGR INNER JOIN SalGrade S On E.Sal Between Losal And Hisal;