2 Replies Latest reply: Mar 7, 2013 1:08 PM by Tobias Arnhold RSS

    FULLT OUTER JOIN over two different selects

    Tobias Arnhold
      Hi,

      I'm facing a problem where I just don't get the grip to solve it.

      I have two selections with different return values. Those value must be compared and joined. To making it simple I make a example where the return of the selects is represented as table1 and table2
      Here is the example:
      table1
      abc_comp_nr, abc_cnt_emp
      12000, 15
      12500, 10
      13000, 30
      13500, 10
      
      table2
      xyz_comp_nr, xyz_cnt_emp
      12000, 15
      13000, 30
      14000, 20
      
      Output should be this:
      comp_nr, abc_cnt_emp, xyz_cnt_emp
      12000, 15, 15
      12500, 10, 0
      13000, 30, 30
      13500, 10, 0
      14000, 0, 20
      comp_nr is the combination of either abc_comp_nr or xyz_comp_nr

      I tried this but didn't work as expected:
      select ???, abc_cnt_emp, xyz_cnt_emp
      from
      table1 FULL OUTER JOIN table2
      ON abc_comp_nr = xyz_comp_nr

      Hope someone had similar issues and found a good way working this out.

      Thanks ahead

      Tobias
        • 1. Re: FULLT OUTER JOIN over two different selects
          Frank Kulash
          Hi, Tobias,
          Tobias Arnhold wrote:
          Hi,

          I'm facing a problem where I just don't get the grip to solve it.

          I have two selections with different return values. Those value must be compared and joined. To making it simple I make a example where the return of the selects is represented as table1 and table2
          Here is the example:
          table1
          abc_comp_nr, abc_cnt_emp
          12000, 15
          12500, 10
          13000, 30
          13500, 10
          
          table2
          xyz_comp_nr, xyz_cnt_emp
          12000, 15
          13000, 30
          14000, 20
          Whenever you have a problem, post CREATE TABLE and INSERT statements for the sample data.
          See the forum FAQ {message:id=9360002}
          Output should be this:
          comp_nr, abc_cnt_emp, xyz_cnt_emp
          12000, 15, 15
          12500, 10, 0
          13000, 30, 30
          13500, 10, 0
          14000, 0, 20
          comp_nr is the combination of either abc_comp_nr or xyz_comp_nr

          I tried this but didn't work as expected:
          select ???, abc_cnt_emp, xyz_cnt_emp
          from
          table1 FULL OUTER JOIN table2
          ON abc_comp_nr = xyz_comp_nr
          In place of ??? you want
          NVL (abc_comp_nr, xyz_comp_nr)
          The first column is supposed to be the common identifier, either abc_comp_nr or xyz_comp_nr. It doesn't matter which one, since the join condition
          ON abc_comp_nr = xyz_comp_nr
          says that they will be the same, except this is a FULL OUTER JOIN, so either one of them could be NULL. So you want to display the non-NULL one when one of them is NULL, and either one (it doesn't matter which) when they are both present. That's just what NVL is doing.

          You can use NVL on the other columns, too, to display 0 instead of NULL when there is no match:
          SELECT   NVL ( abc_comp_nr, xyz_comp_nr)     AS comp_nr
          ,        NVL ( abc_cnt_emp, 0)               AS abc_cnt_emp
          ,      NVL ( xyz_cnt_emp, 0)               AS xyz_cnt_emp
          FROM              table1 
          FULL OUTER JOIN  table2  ON  abc_comp_nr  = xyz_comp_nr
          ;
          However, this will display 0 instead of NULL for abc_cnt_emp and xyz_cnt_emp when those columns are NULL for any reason, not just when they fail the join condition. If the original tables contain NULLs, and you need to preserve those NULLs in the result set, but you want NULLs that were created by the outer join to display as 0, then you might want something like this:
          SELECT   NVL ( abc_comp_nr, xyz_comp_nr)     AS comp_nr
          ,        NVL2 ( abc_comp_nr, abc_cnt_emp, 0)     AS abc_cnt_emp
          ,      NVL2 ( xyz_comp_nr, xyz_cnt_emp, 0)     AS xyz_cnt_emp
          FROM              table1 
          FULL OUTER JOIN  table2  ON  abc_comp_nr  = xyz_comp_nr
          ;
          Whenever you use FULL OUTER JOIN, there's an excellent chance that you'll want to use NVL (or its younger, stronger brother COALESCE, and/or their cousin NVL2) as well.

          Edited by: Frank Kulash on Mar 3, 2013 2:49 PM
          • 2. Re: FULLT OUTER JOIN over two different selects
            Tobias Arnhold
            Hi Frank,

            Thanks for your great explanation and your efforts.

            Tobias