1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

JOINS in SQL ( with ANSI support )

Discussion in 'SQL PL/SQL' started by rajavu, Nov 27, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    JOINS are used in SQL to combine rows fom Multiple table to reqtrieve the required information.There are basically two types of joins Equi joins and Non-equi joins. Equi joins uses '=' sign to combile the rows while Non-equi joins uses other operators.

    But more specifically , Joins can be categorized to the following heads.

    * INNER JOIN
    * NATURAL JOIN
    * LEFT OUTER JOIN
    * RIGHT OUTER JOIN
    * FULL OUTER JOIN
    * CROSS JOIN (Cartesian JOIN)
    * SELF JOIN

    Lets have look at the values in the following Demo tables.

    Code (Text):

    SQL> select empno ,ename,mgr,deptno,job,sal from emp_mgr;

         EMPNO ENAME             MGR     DEPTNO JOB              SAL
    ---------- ---------- ---------- ---------- --------- ----------
          7369 SMITH            7902         20 CLERK            800
          7499 ALLEN            7698         30 SALESMAN        1600
          7521 WARD             7698         30 SALESMAN        1250
          7566 JONES            7839         20 MANAGER         2975
          7654 MARTIN           7698         30 SALESMAN        1250
          7698 BLAKE            7839         30 MANAGER         2850
          7782 CLARK            7839         10 MANAGER         2450
          7788 SCOTT            7566         20 ANALYST         3000
          7839 KING                          10 PRESIDENT       5000
          7844 TURNER           7698         30 SALESMAN        1500
          7876 ADAMS            7788         20 CLERK           1100
          7900 JAMES            7698         30 CLERK            950
          7902 FORD             7566         20 ANALYST         3000
          7934 MILLER           7782         10 CLERK           1300
          2000 RAJUVAN          7902         50 SSE             6000

    15 rows selected.

    SQL> select deptno,dname, loc from dept_mast;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>
     
    There is one major change made in EMP_MGR table in addition to the classic EMP table . New record with department 50 is added. Now there is a recod EMPNO 2000 with department 50 which is missing in DEPT_MAST and there is department 40 in DEPT_MAST against which there is no employee present in EMP_MGR.
    [PAGE]INNER JOIN[/PAGE]
    INNER JOIN

    Inner Join is the sipmles form of join where two or more coulmns of two or more tables joined to form a query.
    In ANSI Standard Qquery it makes use of JOIN or INNER JOIN clause along with ON clause while in traditional INNER Join its makes use of simple WHERE clause.

    ANSI query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E JOIN DEPT_MAST D
      3  ON     (E.DEPTNO = D.DEPTNO);

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK

    14 rows selected.

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E INNER JOIN DEPT_MAST D
      3  ON     (E.DEPTNO = D.DEPTNO);

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK

    14 rows selected.

    SQL>
     
    Traditional query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E, DEPT_MAST D
      3  WHERE  E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK

    14 rows selected.

    SQL>
     
    [PAGE]NATURAL JOIN[/PAGE]
    NATURAL JOIN

    Theoretically natural join is same as Inner join. The only change is in the syntax.

    It can make use either NATURAL JOIN clause or simple JOIN clause. It has no traditional equivalent query.

    If NATURAL JOIN is used , there is no need to refer the column names seperately , as it will join the common fields from the joined tables automatically. If JOIN clause is used , it has to follow USING clause to refer the colum name instead of ON clause.

    ANSI query

    Code (Text):

    SQL> SELECT E.EMPNO , DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E NATURAL JOIN DEPT_MAST D;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK

    14 rows selected.

    SQL> SELECT E.EMPNO , DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E JOIN DEPT_MAST D
      3  USING  (DEPTNO);

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK

    14 rows selected.

    SQL>
     
    [PAGE]LEFT OUTER JOIN[/PAGE]
    LEFT OUTER JOIN

    The LEFT OUTER JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist.

    ANSI queries make use of LEFT OUTER JOIN clause to retrieve the data from the left side of the join, while traditional queries use (+) sign at the right side of join.


    ANSI query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E LEFT OUTER JOIN DEPT_MAST D
      3  ON     E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7934         10 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7902         20 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7369         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          2000         50

    15 rows selected.

    SQL>
     
    Traditional query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E , DEPT_MAST D
      3  WHERE  E.DEPTNO = D.DEPTNO(+);

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7934         10 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7902         20 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7369         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          2000         50

    15 rows selected.

    SQL>
     
    Notice here that it retieves the EMPNO 2000 from EMP_MGR table (left side) while NULL from the DEPT_MAST (right side) as there is no record for department 50 in that table. And also that Dept 40 is not retrieved from DEPT_MAST.
    [PAGE]RIGHT OUTER JOIN[/PAGE]
    RIGHT OUTER JOIN

    The RIGHT OUTER JOIN returns all the rows from the table on the right side of the join, along with the values from the left hand side, or NULLs if a matching row doesn't exist.

    ANSI queries make use of RIGHT OUTER JOIN clause to retrieve the data from the right side of the join, while traditional queries use (+) sign at the left side of join.

    ANSI query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E RIGHT OUTER JOIN DEPT_MAST D
      3  ON     E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK
                          OPERATIONS     BOSTON

    15 rows selected.

    SQL>
     
    Traditional query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E , DEPT_MAST D
      3  WHERE  E.DEPTNO (+)= D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK
                          OPERATIONS     BOSTON

    15 rows selected.

    SQL>
     
    Notice here that it retieves the DEPTNO 40 from DEPT_MAST table (right side) while NULL from the EMP_MGR (right side) as there is no record for department 40 in that table. And also that EMPNO 2000 is not retrieved from EMP_MGR.
    [PAGE]FULL OUTER JOIN[/PAGE]
    FULL OUTER JOIN

    FULL OUTER JOIN returns all the rows from the tables on the both sides of the join along with NULLs if a matching row doesn't exist.

    ANSI queries make use of FULL OUTER JOIN clause to retrieve the data from the both sides of the join. There is no straight equivalent for the FULL OUTER JOIN. But it can be done using UNION of Left and Right outer join queries.


    ANSI query

    Code (Text):

    SQL>SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E FULL OUTER JOIN DEPT_MAST D
      3  ON     E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7934         10 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7902         20 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7369         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          2000         50
                          OPERATIONS     BOSTON

    16 rows selected.

    SQL>
     

    Traditional query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E , DEPT_MAST D
      3  WHERE  E.DEPTNO (+)= D.DEPTNO UNION
      4  SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      5  FROM   EMP_MGR E , DEPT_MAST D
      6  WHERE  E.DEPTNO = D.DEPTNO (+);

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          2000         50
          7369         20 RESEARCH       DALLAS
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 RESEARCH       DALLAS
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 ACCOUNTING     NEW YORK
          7788         20 RESEARCH       DALLAS
          7839         10 ACCOUNTING     NEW YORK
          7844         30 SALES          CHICAGO
          7876         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7902         20 RESEARCH       DALLAS
          7934         10 ACCOUNTING     NEW YORK
                          OPERATIONS     BOSTON

    16 rows selected.

    SQL>
     

    Here notice that both EMPNO 2000 and DEPTNO 40 are retieved using the FULL OUTER JOIN which where left out on LEFT and RIGHT OUTER JOINs.
    [PAGE]CROSS JOIN (Cartesian JOIN)[/PAGE]
    CROSS JOIN

    CROSS JOIN returns the cartesian product from the joining tables.ie each record of every table in the query joins with each records of every other table to produce the cartesian result.the number of records generated on cross join is X(t1}*X(t2)*X(t3)*.... where X(t1) = Number of recods in table t1 , X(t2) = Number of recods in table t2 , X(t3) = Number of recods in table t3 and so on

    ANSI queries make use of CROSS JOIN clause to produce cartesion product from the both sides of the join , while there is no special clause for traditional join . And there wont be any WHERE or ON clause for join.


    ANSI query

    Code (Text):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E CROSS JOIN DEPT_MAST D;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 ACCOUNTING     NEW YORK
          7499         30 ACCOUNTING     NEW YORK
          7521         30 ACCOUNTING     NEW YORK
          7566         20 ACCOUNTING     NEW YORK
          7654         30 ACCOUNTING     NEW YORK
          7698         30 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7788         20 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7844         30 ACCOUNTING     NEW YORK
          7876         20 ACCOUNTING     NEW YORK
          7900         30 ACCOUNTING     NEW YORK
          7902         20 ACCOUNTING     NEW YORK
          7934         10 ACCOUNTING     NEW YORK
          2000         50 ACCOUNTING     NEW YORK
          7369         20 RESEARCH       DALLAS
          7499         30 RESEARCH       DALLAS
          7521         30 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7654         30 RESEARCH       DALLAS
          7698         30 RESEARCH       DALLAS
          7782         10 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7839         10 RESEARCH       DALLAS
          7844         30 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7900         30 RESEARCH       DALLAS
          7902         20 RESEARCH       DALLAS
          7934         10 RESEARCH       DALLAS
          2000         50 RESEARCH       DALLAS
          7369         20 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 SALES          CHICAGO
          7788         20 SALES          CHICAGO
          7839         10 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7876         20 SALES          CHICAGO
          7900         30 SALES          CHICAGO
          7902         20 SALES          CHICAGO
          7934         10 SALES          CHICAGO
          2000         50 SALES          CHICAGO
          7369         20 OPERATIONS     BOSTON
          7499         30 OPERATIONS     BOSTON
          7521         30 OPERATIONS     BOSTON
          7566         20 OPERATIONS     BOSTON
          7654         30 OPERATIONS     BOSTON
          7698         30 OPERATIONS     BOSTON
          7782         10 OPERATIONS     BOSTON
          7788         20 OPERATIONS     BOSTON
          7839         10 OPERATIONS     BOSTON
          7844         30 OPERATIONS     BOSTON
          7876         20 OPERATIONS     BOSTON
          7900         30 OPERATIONS     BOSTON
          7902         20 OPERATIONS     BOSTON
          7934         10 OPERATIONS     BOSTON
          2000         50 OPERATIONS     BOSTON

    60 rows selected.

    SQL>
     
    Traditional query

    Code (Text):

    SQL>SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2  FROM   EMP_MGR E , DEPT_MAST D;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7369         20 ACCOUNTING     NEW YORK
          7499         30 ACCOUNTING     NEW YORK
          7521         30 ACCOUNTING     NEW YORK
          7566         20 ACCOUNTING     NEW YORK
          7654         30 ACCOUNTING     NEW YORK
          7698         30 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7788         20 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7844         30 ACCOUNTING     NEW YORK
          7876         20 ACCOUNTING     NEW YORK
          7900         30 ACCOUNTING     NEW YORK
          7902         20 ACCOUNTING     NEW YORK
          7934         10 ACCOUNTING     NEW YORK
          2000         50 ACCOUNTING     NEW YORK
          7369         20 RESEARCH       DALLAS
          7499         30 RESEARCH       DALLAS
          7521         30 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7654         30 RESEARCH       DALLAS
          7698         30 RESEARCH       DALLAS
          7782         10 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7839         10 RESEARCH       DALLAS
          7844         30 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7900         30 RESEARCH       DALLAS
          7902         20 RESEARCH       DALLAS
          7934         10 RESEARCH       DALLAS
          2000         50 RESEARCH       DALLAS
          7369         20 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7566         20 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7782         10 SALES          CHICAGO
          7788         20 SALES          CHICAGO
          7839         10 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7876         20 SALES          CHICAGO
          7900         30 SALES          CHICAGO
          7902         20 SALES          CHICAGO
          7934         10 SALES          CHICAGO
          2000         50 SALES          CHICAGO
          7369         20 OPERATIONS     BOSTON
          7499         30 OPERATIONS     BOSTON
          7521         30 OPERATIONS     BOSTON
          7566         20 OPERATIONS     BOSTON
          7654         30 OPERATIONS     BOSTON
          7698         30 OPERATIONS     BOSTON
          7782         10 OPERATIONS     BOSTON
          7788         20 OPERATIONS     BOSTON
          7839         10 OPERATIONS     BOSTON
          7844         30 OPERATIONS     BOSTON
          7876         20 OPERATIONS     BOSTON
          7900         30 OPERATIONS     BOSTON
          7902         20 OPERATIONS     BOSTON
          7934         10 OPERATIONS     BOSTON
          2000         50 OPERATIONS     BOSTON

    60 rows selected.

    SQL>
     
    Note that the number of records generated are (15 records from EMP_MGR) * (4 records from DEPT_MAST) = 60.
    [PAGE]SELF JOIN[/PAGE]
    SELF JOIN

    SELF JOIN is used where one table is joined to itself for retrieving some records. It is usually done to seperate two or more entity that contains in the same record.

    Following EMP_MGR table is Self jnoined to seprate the two entities (Employee and Manager) from the same records

    Code (Text):

    SQL> SELECT E.EMPNO , E.ENAME , M.EMPNO MGR , M.ENAME MNAME
      2  FROM   EMP_MGR E , EMP_MGR M
      3  WHERE  E.MGR  = M.EMPNO;

         EMPNO ENAME             MGR MNAME
    ---------- ---------- ---------- ----------
          7902 FORD             7566 JONES
          7788 SCOTT            7566 JONES
          7900 JAMES            7698 BLAKE
          7844 TURNER           7698 BLAKE
          7654 MARTIN           7698 BLAKE
          7521 WARD             7698 BLAKE
          7499 ALLEN            7698 BLAKE
          7934 MILLER           7782 CLARK
          7876 ADAMS            7788 SCOTT
          7782 CLARK            7839 KING
          7698 BLAKE            7839 KING
          7566 JONES            7839 KING
          2000 RAJUVAN          7902 FORD
          7369 SMITH            7902 FORD

    14 rows selected.

    SQL> SELECT E.EMPNO , E.ENAME , M.EMPNO MGR , M.ENAME MNAME
      2  FROM   EMP_MGR E LEFT OUTER JOIN EMP_MGR M
      3  ON     (E.MGR  = M.EMPNO);

         EMPNO ENAME             MGR MNAME
    ---------- ---------- ---------- ----------
          7902 FORD             7566 JONES
          7788 SCOTT            7566 JONES
          7900 JAMES            7698 BLAKE
          7844 TURNER           7698 BLAKE
          7654 MARTIN           7698 BLAKE
          7521 WARD             7698 BLAKE
          7499 ALLEN            7698 BLAKE
          7934 MILLER           7782 CLARK
          7876 ADAMS            7788 SCOTT
          7782 CLARK            7839 KING
          7698 BLAKE            7839 KING
          7566 JONES            7839 KING
          2000 RAJUVAN          7902 FORD
          7369 SMITH            7902 FORD
          7839 KING

    15 rows selected.

    SQL>