[Oracle] With 문 사용(Full Join) - 카테시안 조인

with test1
as
(
    select 'EQ1' as ttt from dual 
    union
    select 'EQ2' as ttt from dual
),
test2
as
(
    select * from "테스트 할 테이블명"

)
select *  from test1, test2
where ttt = 'EQ1'

 

임시테이블 EQ1, EQ2를 만들어서,  테스트할 테이블과 JOIN해서 보여줄때 사용함.


 =========================================== 응 용 ========================================>

with test1
as
(
    select 'EQ_A' as eq  ,'F' as ilsang from dual
    union
    select 'EQ_B' as eq,  'D' as ilsang from dual
    union
    select 'EQ_C' as eq,  'E' as ilsang from dual 
)
,
test2
as
(  
    select  ' ' as il, 'S' as vu from dual    -- 널값을 처리
    union
    select  ' ' as il, 'B' as vu from dual
    union
    select  ' ' as il, 'U' as vu from dual
    union         
    select  ' ' as il, 'X' as vu from dual
    union                           
    select  'F' as il, 'F-1' as vu from dual
    union
    select  'F' as il, 'F-2' as vu from dual
    union
    select  'D' as il, 'D-1' as vu from dual
    union
    select  'D' as il, 'D-2' as vu from dual
    union
    select  'D' as il, 'D-3' as vu from dual
    union
    select  'E' as il, 'E-1' as vu from dual
)

select  t1.eq, t2.vu
from    test1 t1, test2 t2 
where   t1.ilsang = t2.il
        or t2.il=' '
order by t1.eq, t2.il