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;