Geeks - Tip of the Day

[krishnaindia2007]

Join Types - III

Outer Join
outer join extends the results of inner join. Along with inner join results it will also return non matched rows from the table with outer join operator(+). Missing values are filled with null.

Left Outer Join
It returns row that meets the join condition + rest of the rows from left table

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from   emp a , dept b 
     where  b.deptno = a.deptno(+); 
Ansi Syntax 
     select b.dname, a.ename, a.job, a.sal 
     from   dept b left outer join emp a 
     on a.deptno = b.deptno;

Right Outer Join
It returns row that meets the join condition + rest of the records from table on the right side.

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from emp a , dept b 
     where a.deptno(+) = b.deptno; 
Ansi Syntax 

    select b.dname, a.ename, a.job, a.sal 
    from   emp a right outer join dept b 
    on     a.deptno = b.deptno;

Full Outer Join
It returns rows that meet the join codition + rest of the records from both the tables

 
Ansi Syntax 
select b.dname, a.ename, a.job, a.sal 
from   emp a full outer join dept b 
on     a.deptno = b.deptno;

[krishnaindia2007]

JOIN TYPES - IV

Anti Join
It returns rows from first table for which there are no corresponding rows in the second table. Anti joins are written using NOT EXISTS or NOT IN.

Ex:-   SELECT a.dname 
       FROM   dept a
       WHERE  NOT EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);
Which is same as 
        SELECT dname
        FROM   dept 
        MINUS
        SELECT b.dname
        FROM   emp a, dept b
        WHERE  a.deptno = b.deptno;

Semi Join
It return rows from the first table for which one or more joins are found in the second table. It is written using IN or EXISTS.

Ex:- SELECT a.dname 
     FROM   dept a
     WHERE EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);