Self Joins:
Ø It is a join of a table itself.
Ø The Same table appears twice in the
FROM Clause and is Followed by table aliases.
Ø The table aliases must quality the
column names in the join condition.
Ø To perform a self join, oracle
combines and returns rows of the table that satisfy the join condition.
Syntax:
Ex:
- Select Columns from Table1 T1,
Table1 T2 where T1.Column1 = T2.Column2;
Illustrations:
- Select E1.Ename “Employee
Name”, E2.Ename “Managers Name” From Emp E1, Emp E2 where E1.Mgr =
E2.Empno;
- Select E1.Ename ||’`s Manager
is ’|| E2.Ename “Employees and Managers” From Emp E1, Emp E2 where E1.Mgr
= E2.Empno;
- Select E1.Ename ||’ works for ’
||E2.Ename “Employees and Managers” From Emp E1, Emp E2 where (E1.Mgr =
E2.Empno) And E1.Job = ’CLERK’;
Non Equi Join:
Ø It is a join condition that is
executed when no column in one table corresponds directly to a column in the
other table.
Ø The data in the tables directly not
related but indirectly or logically related through proper values.
Ex:
- Select E.Ename, E.Sal, S.Grade
From Emp E, Salgrade S where E.sal Between S.losal and S.hisal;
- Select E.Ename, E.Sal, S.grade
From Emp E, Salgrade S where (E.Sal >= S.losal And E.Sal <= S.hisal)
and S.grade = 1;
Outer Joins:
Ø An outer join extends the result of
a simple or inner join.
Ø An outer Join returns all rows that
satisfy the join condition and also those rows from one table for which no rows
from the other satisfy the join condition.
Ø To perform an outer join of tables
‘A’ and ‘B’ and returns all rows from ‘A’ apply the outer join operator ‘(+)’
to all columns of table ‘B’.
Ø For all rows in ‘A’ that have no
matching rows in ‘B’, oracle returns NULL for any select list expressions
containing columns of ‘B’.
Syntax:
Ex:
- Select Table1.Column,
Table2.Column From Table1, Table2 where Table1.Column (+) = Table2.Column;
- Select Table1.Column,
Table2.Column From Table1, Table2 where Table1.Column = Table2.Column (+);
Rules And Restrictions:
Ø The (+) operator can appear only in
the where clause.
Ø The (+) operator can appear in the
context of the left correlation in the From Clause, and can be applied only to
a column of a table or view.
Ø If ‘A’ and ‘B’ are joined by
multiple join conditions, we must use the (+) operator in all of these
conditions.
Ø The (+) operator can be applied only
to a column, not to an arbitrary expressions.
Ø A condition containing (+) operator
cannot be combined with another condition using OR logical operator.
Ø A condition cannot use the IN
Comparison operator to compare a column marked with (+) operator with an
expression.
Ø A condition cannot compare any
column marked with the (+) operator with a sub query.
Ex:
- Select E.Ename, D.Deptno,
D.Dname From Emp E, Dept D where E.Deptno (+) = D.Deptno Order by
E.Deptno;
- Select E.Ename, D.Deptno,
D.Dname From Emp E, Dept D where E.Deptno(+) = D.Deptno And E.Deptno (+) =
10 Order by E.Deptno;
- Select E.Ename, D.Deptno,
D.Dname From Emp E, Dept D where E.Deptno = D.Deptno (+) And E.Deptno(+) =
10 order by E.Deptno;
- Select E.Ename Employee,
NVL(M.Ename, ’Supreme Authority’) Manager From Emp E, Emp M where E.MGR =
M.Empno (+);
Joining Data From More Than Two Tables:
Ø Joins can be established on more
than two tables.
Ø The Join is first executed upon the
two most relevant tables and then the result is applied upon the third table.
Ex:
- Select E.Ename, E.Deptno,
M.Ename Manager, M.Deptno From Emp E, Dept D, Emp M where E.MGR = M.Empno
and E.Deptno = D.Deptno;
- Select E.Ename Ename, Dname,
E.Sal Esal, SE.Grade SEGrade, M.Sal Msal, SM.Grade MGrade From Emp E,Dept
D, Emp M, SalGrade SE, SalGrade SM where E.Deptno = D.Deptno And E.MGR =
M.Empno And E.Sal Between SE.losal and SE.Hisal And M.Sal Between SM.losal
and SM.Hisal;