Joins in oracle apps_2

There are four types of joins

1)     Equi join

2)     Non equi join

3)     Outer join

4)     Self join

1)    Equi Join

Two tables are joined with equal to operator

In 8i

Select a.stno,a.stname,a.address,b.eng,b.tam,b.mat

from stu_mas a, stu_dtl b

where a.stno = b.stno;

Natural Join:

          Natural join is also like an equi join. Here the join  happens automatically based

on columns in the two tables  provided both the columns have the same name

and datatype

          select empno,ename,dname from emp natural join dept;

          You can use either a USING clause (or) ON clause

          USING à You can specify the common column in both the tables.

          ON       à  You have to give the join condition.

          Example :

                  

          select s1.stname,s1.address,s2.eng,s2.kan,s2.mat,s3.male_female,s3.dob

          from  stu_mas s1  join stu_dtl s2

                                       on (s1.stno = s2.stno)

                                       join stu_personal s3

                                       on (s2.stno = s3.stno);

    Non – Equi Join

      Any operator other than equal to operator used , is a non equi join

      In 9i

     Select a.stno,a.stname,a.address,b.eng,b.tam from stu_mas a,stu_dtl b

     where b.eng >= 40 and b.kan >= 40

1)    Outer Join

The resultant of the query will be the rows which do not satisfy the join condition.

There are 3 types of outer joins

1)     Left Outer join

2)     Right outer join

3)     Full outer join

1)    Right outer join

                   This will return all the rows in the right table even though there is no          

                   match in the left table.

     In 8i

     Select  a.stno,a.stname,b.eng,b.kan from stu_mas a, stu_dtl b

     Where a.stno = b.stno(+);

     In 9i

     Select stno,stname,address,eng,kan from stu_mas right outer join stu_dtl

     Using ( stno);

2)    Left Outer Join

This will return all the rows in the left table even though there is no match in the right table.

In 8i

Select a.stno, a.stname, b.eng,b.tam from stu_mas a, stu_dtl b

Where a.stno(+) = b.stno;

In 9i

      Select stno,stname,address,eng,tam from stu_mas left outer join stu_dtl

      Using ( stno);

3)    Full outer join

It retrieves all the rows from the right and left table even though there is no match in the right and left table.

In 8i

Select a.stno,a.stname,a.address,b.eng,b.tam from stu_mas a ,fees b

Where a.stno = b.stno(+)

Union

Select a.stno,a.stname,a.address,b.eng,b.tam from stu_mas a, fees b

Where a.stno(+) = b.stno;

                     In 9i

       Select stno , stname , address, eng, kan from stu_mas  full outer join

                     stu_dtl using (stno)

Self Join

     A table is joined to itself

     select a.stno,a.stname,b.address from stu_mas a,stu_mas b

     where a.stno = b.stno