Suresh

• Jul 4th, 2006

 

Outer Join - method of retreiving selected rows from one table that doesn't match rows with the other table.

Ex: Consider A & B two tables

SELECT eno, ename, a.dno, dname 

FROM a,b

WHERE a.dno=b.dno;

Output:

Eno        Ename           Dno              Dname

100        John               10               Accounts

101        Michael            20               Sales

                                   30              Production                

                                   40              Purchase

In outer join 2 types r there:

Left Outer Join & Right outer Join

Ex: LeftOuter Join

SELECT eno, ename, a.dno, dname 

FROM a,b

WHERE a.dno(+)=b.dno;

Display all the rows from "Table A".

Output:

Eno        Ename           Dno              Dname

100        John               10               Accounts

101        Michael            20               Sales

102        Anni                20               Sales                

103        Sonia              

105        Mann

Ex: Right Outer Join

SELECT eno, ename, a.dno, dname 

FROM a,b

WHERE a.dno=(+)b.dno;

Display all the rows from "Table B".

Output:

Eno        Ename           Dno              Dname

100        John               10               Accounts

101        Michael            20               Sales

102        Anni                20               Sales                

                                   30              Production                

                                   40              Purchase

Suresh