Partitioned Outer Join

[원쿼리맨]

Partitioned Outer Join



with t as (
select level lv from dual
connect by level<=12)
, x as (
select 1 id, 1 lv from dual union all
select 1, 3 lv from dual union all
select 2, 9 lv from dual)
select x.id, t.lv, x.lv
  from t
  left join x
  partition by (x.id)
    on t.lv = x.lv
order by x.id, t.lv    





        ID         LV       LV_1
---------- ---------- ----------
         1          1          1
         1          2          
         1          3          3
         1          4          
         1          5          
         1          6          
         1          7          
         1          8          
         1          9          
         1         10          
         1         11          
         1         12          
         2          1          
         2          2          
         2          3          
         2          4          
         2          5          
         2          6          
         2          7          
         2          8          
         2          9          9
         2         10          
         2         11          
         2         12