This discussion is archived
2 Replies Latest reply: Feb 6, 2013 3:43 PM by Frank Kulash RSS

Equi Join and Outer join using outer keyword

622930 Newbie
Currently Being Moderated
Hi,

First lets take the create statment for scott schema.
create table scott.emp_details(empno number, bonus_date date);

Insert Into Scott.Emp_Details Values(7369, To_Date('01-jan-2013'));
Insert Into Scott.Emp_Details Values(7499, To_Date('05-jan-2013'));
Insert Into Scott.Emp_Details Values(7521, To_Date('10-jan-2013'));
Insert Into Scott.Emp_Details Values(7566, To_Date('01-feb-2013'));
Insert Into Scott.Emp_Details Values(7654, To_Date('05-feb-2013'));
commit;
lets also consider the basic scott.emp and scott.dept tables

Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.

Please suggest
  • 1. Re: Equi Join and Outer join using outer keyword
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    sri wrote:
    Hi,

    First lets take the create statment for scott schema.
    create table scott.emp_details(empno number, bonus_date date);
    
    Insert Into Scott.Emp_Details Values(7369, To_Date('01-jan-2013'));
    Insert Into Scott.Emp_Details Values(7499, To_Date('05-jan-2013'));
    Insert Into Scott.Emp_Details Values(7521, To_Date('10-jan-2013'));
    Insert Into Scott.Emp_Details Values(7566, To_Date('01-feb-2013'));
    Insert Into Scott.Emp_Details Values(7654, To_Date('05-feb-2013'));
    commit;
    It's best not to create your own tables in Oracle-supplied schemas, such as SCOTT. Use your own schema for your own tables.
    lets also consider the basic scott.emp and scott.dept tables
    I see; you're using the standard scott,emp and scott.dept tables, plus the emp_details table you posted above.
    Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

    The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.

    Please suggest
    Thanks for posting the sample data. Don't forget to post the exact output you want from that sample data.
    Do you want something like this?
    `    EMPNO ENAME          DEPTNO DNAME          BONUS_DAT
    ---------- ---------- ---------- -------------- ---------
          7369 SMITH              20 RESEARCH       01-JAN-13
          7499 ALLEN              30 SALES          05-JAN-13
          7521 WARD               30 SALES          10-JAN-13
          7566 JONES              20 RESEARCH       01-FEB-13
          7654 MARTIN             30 SALES          05-FEB-13
          7698 BLAKE              30 SALES
          7782 CLARK              10 ACCOUNTING
          7788 SCOTT              20 RESEARCH
          7839 KING               10 ACCOUNTING
          7844 TURNER             30 SALES
          7876 ADAMS              20 RESEARCH
          7900 JAMES              30 SALES
          7902 FORD               20 RESEARCH
          7934 MILLER             10 ACCOUNTING
                                  40 OPERATIONS
    If so, here's one way to do it:
    SELECT       e.empno, e.ename     -- or whatever columns you want
    ,       d.deptno, d.dname     -- or whatever columns you want
    ,       ed.bonus_date
    FROM           scott.dept  d
    LEFT OUTER JOIN      scott.emp   e   ON  e.deptno     = d.deptno
    LEFT OUTER JOIN      emp_details ed      ON  ed.empno     = e.empno
    ORDER BY  e.empno
    ;
  • 2. Re: Equi Join and Outer join using outer keyword
    odie_63 Guru
    Currently Being Moderated
    Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

    The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.
    The requirement is not weird but sounds very much like an homework assignment.
    Please suggest
    What have you tried so far?

    Edit:
    Oh well... Frank has already replied.
    If you like being spoon-fed, so be it.

    Edited by: odie_63 on 6 févr. 2013 22:27

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points