3 Replies Latest reply: Oct 26, 2010 11:17 AM by johnnie d RSS

    Outer Join and Duplicates

    797345
      create table tab_A
      (empname varchar2(40)
      );
      
      
      create table tab_B
      (empname varchar2(40)
      );
      
      ----- Insert into tab_A
      insert into tab_A values ('A');
      insert into tab_A values ('B');
      insert into tab_A values ('C');
      insert into tab_A values ('D');
      insert into tab_A values ('E');
      insert into tab_A values ('A');
      insert into tab_A values ('B');
      insert into tab_A values ('C');
      
      
      --- Insert into tab_B
      insert into tab_B values ('A');
      insert into tab_B values ('B');
      insert into tab_B values ('C');
      insert into tab_B values ('D');
      
      
      SQL > select * From tab_A;
      
      EMPNAME
      ---------------------------------
      A
      B
      C
      D
      E
      A
      B
      C
      
      8 rows selected.
      
      
      
      
      SQL> select * From tab_B;
      
                     EMPNAME
                     -------
                     A
                     B
                     C
                     D
                     LEFT OUTER JOIN will return all the matching records between TAB_A and TAB_B and then return all records in TAB_A.
      But it is returning duplicate records from TAB_A (ie. values A, B and C). Is there a way to eliminate this without using DISTINCT
      SQL > select tab_A.empname
        2  from TAB_A left outer join TAB_B
        3  on (tab_a.empname=tab_b.empname);
      
      EMPNAME
      ----------------------------------------
      A
      A
      B
      B
      C
      C
      D
      E
      
      8 rows selected.
        • 1. Re: Outer Join and Duplicates
          Frank Kulash
          Hi,

          What's wrong with SELECT DISTINCT?

          At any rate, GROUP BY gets distinct values:
          SELECT       ename
          FROM       tab_a
          GROUP BY  ename
          ;
          Note that there's no need to use tab_b to get these resutls. "FROM tab_a LEFT OUTER JOIN tab_b" means you want all the rows from tab_a, regardless of whether or not they have a match in tab_b. Since you're not interested in any data from tab_b, and you specifically don't want any replication of rows that might be caused by 2 or more rows in tab_b matching the same row in tab_a, then you'll get the same results with or without the outer join.
          • 2. Re: Outer Join and Duplicates
            Salvo
            No you can't, to avoid using distinct clause, you can opt for group by.

            Bye
            • 3. Re: Outer Join and Duplicates
              johnnie d
              if there are duplicates in TAB_A, you'll get 'em in any join for the same column on TAB_B

              you can a)remove the duplicates in TAB_A, b) use a DISTINCT c) GROUP BY or d) a UNION of TAB_A with itself -
              have to say , DISTINCT looks fine to me