Friday, October 28, 2011

ORACLE SQL TUTORIAL: JOINS




The purpose of a join is to combine the data across tables.
A join is actually performed by the where clause which combines the specified rows of tables.
If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES
*      Equi join
*      Non-equi join
*      Self join
*      Natural join
*      Cross join
*      Outer join
Ø  Left outer
Ø  Right outer
Ø  Full outer
*      Inner join
*      Using clause
*      On clause

Assume that we have the following tables.
SQL> select * from dept;

    DEPTNO DNAME      LOC
     ------ ---------- ----------
        10   mkt        hyd
        20   fin        bang
        30   hr         bombay

SQL> select * from emp;

       EMPNO   ENAME      JOB       MGR     DEPTNO
      ---------- ---------- ---------- ---------- ----------
       111         saketh     analyst           444         10
       222         sudha     clerk                333         20
       333         jagan      manager         111         10
       444         madhu    engineer         222         40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex:
     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

          EMPNO     ENAME      JOB    DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
                111               saketh    analyst    mkt        hyd
                333               jagan      manager  mkt        hyd
                222               sudha      clerk        fin        bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
                111               saketh    analyst    mkt        hyd
                333               jagan      manager  mkt        hyd
                222               sudha      clerk        fin        bang

ON CLAUSE

SQL>  select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

          EMPNO     ENAME      JOB    DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
                111               saketh    analyst    mkt        hyd
                333               jagan      manager  mkt        hyd
                222               sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex:
     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
       222    sudha      clerk          mkt        hyd
       444    madhu     engineer   mkt        hyd
       444    madhu     engineer   fin          bang
       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.

Ex:
     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where
             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO
     ---------- ---------- ---------- ----------
       111 jagan      analyst         10
       222 madhu      clerk           40
       333 sudha      manager      20
       444 saketh     engineer      10
NATURAL JOIN

Natural join compares all the common columns.


Ex:
     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC
    ---------- ---------- ---------- ---------- ----------
       111 saketh     analyst     mkt        hyd
       333 jagan      manager   mkt        hyd
       222 sudha      clerk         fin          bang

CROSS JOIN

This will gives the cross product.

Ex:
     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC
---------- ---------- ---------- ---------- ----------
       111     saketh   analyst      mkt        hyd
       222     sudha    clerk          mkt        hyd
       333     jagan     manager   mkt        hyd
       444     madhu   engineer   mkt        hyd
       111     saketh   analyst      fin          bang
       222     sudha    clerk          fin          bang
       333     jagan     manager   fin          bang
       444     madhu   engineer   fin          bang
       111     saketh   analyst      hr           bombay
       222     sudha    clerk          hr           bombay
       333     jagan     manager   hr           bombay
       444     madhu   engineer   hr           bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.


LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:
     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d
             on(e.deptno=d.deptno);
Or
      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);
    

     EMPNO     ENAME   JOB       DNAME      LOC
     ---------- ---------- ---------- ---------- ----------
       111 saketh    analyst       mkt        hyd
       333 jagan      manager    mkt        hyd
       222 sudha     clerk           fin          bang
       444 madhu    engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:
     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d
              on(e.deptno=d.deptno);
Or
      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;

     EMPNO    ENAME     JOB      DNAME      LOC
     ---------- ---------- ---------- ---------- ----------
       111 saketh     analyst      mkt        hyd
       333 jagan       manager   mkt        hyd
       222 sudha      clerk          fin          bang
                                                       hr           bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:
     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d
              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC
---------- ---------- ---------- ---------- ----------
       333     jagan     manager    mkt        hyd
       111     saketh   analyst       mkt        hyd
       222     sudha    clerk           fin        bang
       444     madhu   engineer  hr         bombay

INNER JOIN

This will display all the records that have matched.

Ex:
     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC
     ---------- ---------- ---------- ---------- ----------
       111 saketh     analyst      mkt       hyd
       333 jagan       manager   mkt       hyd
       222 sudha      clerk          fin         bang


0 Responses to “ORACLE SQL TUTORIAL: JOINS”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search