Self, Equi, NON Equi and Outer Joins

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;