ANSI 내부조인

게시일: Sep 05, 2011 11:14:37 PM

p247에 보면 ANSI 내부조인이 소개되어 있는데요, 사용할 일은 없을 것 같지만 테스트는 한번 해 봤습니다.

select b.p_nm, a.*

from

(

select to_date('2021-03-01', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-02', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-01', 'yyyy-mm-dd') i_date, '2' p_id from dual union all

select to_date('2021-03-02', 'yyyy-mm-dd') i_date, '3' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, '3' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, 'e' p_id from dual -- 마스터에 없음

) a -- 제품 입고 현황 Table

inner join

(

select '1' p_id, 'p_1' p_nm from dual union all

select '2' p_id, 'p_2' p_nm from dual union all

select '3' p_id, 'p_3' p_nm from dual union all

select '4' p_id, 'p_4' p_nm from dual

) b -- 제품정보 Master

on b.p_id = a.p_id

-- where a.p_id = '1'

order by

b.p_nm

, a.i_date

;

select b.p_nm, a.i_date

from

(

select to_date('2021-03-01', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-02', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, '1' p_id from dual union all

select to_date('2021-03-01', 'yyyy-mm-dd') i_date, '2' p_id from dual union all

select to_date('2021-03-02', 'yyyy-mm-dd') i_date, '3' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, '3' p_id from dual union all

select to_date('2021-03-03', 'yyyy-mm-dd') i_date, 'e' p_id from dual -- 마스터에 없음

) a -- 제품 입고 현황 Table

inner join

(

select '1' p_id, 'p_1' p_nm from dual union all

select '2' p_id, 'p_2' p_nm from dual union all

select '3' p_id, 'p_3' p_nm from dual union all

select '4' p_id, 'p_4' p_nm from dual

) b -- 제품정보 Master

using (p_id)

-- where p_id = '1'

order by

b.p_nm

, a.i_date

;