Difficult join (SQL)

user_17306445 0
Viewed 77

I have 2 sql data set:

Data set 1:

select * from (
       Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
       Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
       Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
       Select 3 rn, 'b' c, 1 Val from dual  

) t1

Data set 2:

select * from ( 
       Select 'b' c from dual  UNION ALL
       Select 'r' c from dual  UNION ALL
       Select 'w' c from dual ) t2

I need to get following data set:

rn  c   val
1   b   3 
1   r   3 
1   w   3 
2   b   2 
2   r   2 
2   w   null 
3   b   1 
3   r   null 
3   w   null

I tried do this with the following sql, but it doesn't work.

select t1.*, t2.*
FROM (
       Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
       Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
       Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
       Select 3 rn, 'b' c, 1 Val from dual  

) t1 right join ( 
       Select 'b' c from dual  UNION ALL
       Select 'r' c from dual  UNION ALL
       Select 'w' c from dual ) t2 on   t1.c=t2.c 
       order by 1,2

Please, help me fix my query. I use Oracle database.

join oracle sql
2 Answers

This is the exact use case for a partitioned outer join. It should be significantly more efficient for large data sets than the CROSS JOIN approach.

The syntax is like this:

select t1.rn, 
       t2.c,
       t1.val
from   t1 partition by (rn) right join t2 on t2.c = t1.c;

The PARTITION BY keyword tells Oracle to repeat the outer join for each distinct value of t1.rn.

Here is a full example:

with t1 as (
     Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
     Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
     Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
     Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
     Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
     Select 3 rn, 'b' c, 1 Val from dual
  ),
  t2 as (
     Select 'b' c from dual  UNION ALL
     Select 'r' c from dual  UNION ALL
     Select 'w' c from dual
  )
 select t1.rn, 
        t2.c,
        t1.val
 from   t1 partition by (rn) right join t2 on t2.c = t1.c;

+----+---+-----+
| RN | C | VAL |
+----+---+-----+
|  1 | b |   3 |
|  1 | r |   3 |
|  1 | w |   3 |
|  2 | b |   2 |
|  2 | r |   2 |
|  2 | w |   - |
|  3 | b |   1 |
|  3 | r |   - |
|  3 | w |   - |
+----+---+-----+
user_51744360

With a little help of cross-join between those two tables (so that you'd get all [rn, c] combinations; that's my temp CTE) and then outer-joining it to t1, you'd get the result.

Sample data in lines #1 - 14; query you might be interested in begins at line #15 (just precede it with the WITH keyword):

SQL> with t1 as (
  2         Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
  3         Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
  4         Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
  5         Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
  6         Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
  7         Select 3 rn, 'b' c, 1 Val from dual
  8  ),
  9  t2 as (
 10         Select 'b' c from dual  UNION ALL
 11         Select 'r' c from dual  UNION ALL
 12         Select 'w' c from dual
 13  ),
 14  --

 15  temp as
 16    (select distinct a.rn, b.c
 17     from t1 a cross join t2 b
 18    )
 19  select t.rn, t.c, a.val
 20  from temp t left join t1 a on a.rn = t.rn and a.c = t.c
 21  order by t.rn, t.c;

        RN C        VAL
---------- - ----------
         1 b          3
         1 r          3
         1 w          3
         2 b          2
         2 r          2
         2 w
         3 b          1
         3 r
         3 w

9 rows selected.

SQL>
user_90979060
Related