Oracle OUTER JOIN 정리 5 M:M 연결문제해결
Updated:
1. 예제 설명
모든 RDBMS에서 2개의 테이블을 연결하여 데이타를 추출할때에는 테이블의 데이타 관계가 1 대 M, 1 대 1 관계는 문제없이 조회됩니다. 그러나 M 대 M 관계일경우에는 잘못된값(Wrong Resluts)이 조회되는 심각한 상황이 벌어진다. 예를 들어 부모쪽 테이블의 데이타가 1개이고 자식테이블은 연결데이타가 2개 일경우 1 * 2 , 즉 2건이 조회됩니다.
만일 잘못된 데이타가 부모쪽 테이블에 들어가 2개가 되고 자식테이블의 데이타가 2개 일경우 2 * 2, 즉 4건의 데이타가 조회됩니다. 잘못된 데이타가 나타나는 것입니다. 이런경우 데이타를 클린징하지 않고 SQL 형태만 변경하여 정확한 값을 추출할수는 없을까? 물론 가능합니다.
아래의 예제는 판매계획과 판매실적을 정상적인 조인형태의 SQL을 사용하여 예제입니다. 정상적으로 1 대 M 관계의 테이블관계에서는 조인문장으로 정확한 값을 추출할수 있습니다. 그러나 테이블간 관계가 어떤 이유로 인해 M 대 M 관계가 됩니다면 정상적인 조인 SQL을 사용하여도 잘못된값이 조회되는 사태가 벌어진다. 이때에는 OUTER JOIN이나 UNION ALL을 사용하면 정확한 값을 조회할수 있습니다. 상세한 설명은 아래의 예를 보면서 알아봅니다.
가. 예제 테이블 설명
판매계획은 상품과 년월, 판매수량을 가지고 있는 테이블입니다. 상품과 계획연월칼럼이 PK이나 테스트를 위해 실제 PK는 생성하지 않는다. 기타, 20120101 레코드가 원래 계획수량이 300으로 입력되어야 하나, 테스트를 위해 3개에 결쳐 100개씩 입력한다. 첼로, 오르간도 2개씩 값을 입력하여 잘못된값이 조회되도록 한다. 판매실적 테이블은 상품과 판매실적 칼럼이 PK입니다. 데이타는 판매계획테이블의 기타, 20120101의 계획수량이 100개씩 3개가 있고, 판매실적 테이블의 기타 상품이 20120101에 1개의 100개 판매수량만 있습니다. 즉 부모-자식테이블 관계가 3 대 1인것입니다. 오르간은 2 대 2, 첼로는 2 대 2 입니다. 이들을 정상적인 조인을 해서 얼마나 값이 잘못되어 나타나는지 관찰해봅니다. 최종결과 테이블처럼 데이타가 조회하면 됩니다.
- 판매계획
| 상품 | 계획연월 | 판매부서 | 계획수량 |
|---|---|---|---|
| 기타 | 20120101 | 판매부 | 100 |
| 기타 | 20120101 | 판매부 | 100 |
| 기타 | 20120101 | 판매부 | 100 |
| 기타 | 20120102 | 판매부 | 300 |
| 기타 | 20120120 | 판매부 | 100 |
| 기타 | 20120124 | 판매부 | 100 |
| 오르간 | 20120122 | 영업부 | 300 |
| 오르간 | 20120122 | 영업부 | 300 |
| 첼로 | 20120104 | 총무부 | 400 |
| 첼로 | 20120104 | 총무부 | 400 |
| 피아노 | 20120121 | 인사부 | 100 |
| 피아노 | 20120124 | 인사부 | 100 |
- 판매실적
| 상품 | 판매연월 | 판매채널 | 판매실수량 |
|---|---|---|---|
| 기타 | 20120101 | 판매부 | 200 |
| 기타 | 20120102 | 판매부 | 100 |
| 기타 | 20120120 | 인사부 | 100 |
| 아쟁 | 20120101 | 영업부 | 100 |
| 오르간 | 20120122 | 영업부 | 300 |
| 오르간 | 20120122 | 영업부 | 300 |
| 첼로 | 20120104 | 총무부 | 400 |
| 첼로 | 20120104 | 총무부 | 400 |
| 피아노 | 20120121 | 인사부 | 100 |
| 피아노 | 20120124 | 인사부 | 100 |
| 해금 | 20120101 | 총무부 | 300 |
- 최종결과
| 상품 | 계획연월 | 판매계획 | 판매실적 |
|---|---|---|---|
| 기타 | 20120101 | 300 | 200 |
| 기타 | 20120102 | 300 | 100 |
| 기타 | 20120120 | 100 | 100 |
| 기타 | 20120124 | 100 | 0 |
| 아쟁 | 20120101 | 0 | 100 |
| 오르간 | 20120122 | 600 | 600 |
| 첼로 | 20120104 | 800 | 800 |
| 피아노 | 20120121 | 100 | 100 |
| 피아노 | 20120124 | 100 | 100 |
| 해금 | 20120101 | 0 | 300 |
나. 조인을 사용할경우
아래의 SQL을 사용하여 조인한 결과를 보면 3 대 1 의 관계의 기타 데이타는 3건으로 나탔고 이 레코드중 판매실적은 200개가 3번 나타난다. 최종적으로 이값을 SUM하면 600이라는 잘못된 값이 되는것입니다. 첼로 레코드는 판매계획, 판매수량이 400개로 4번 나타나고 오르간도 4개씩 데이타가 조회되어 나타난다. 주로 PK가 잘못 설정되어 있거나, PK가 없거나, FK가 없는 경우 이런 사태를 발생시킵니다.
select nvl(a.상품, b.상품) as 상품 ,
nvl(a.계획연월, b.판매연월) as 연월 ,
nvl(계획수량, 0) 계획수량 ,
nvl(판매수량, 0) 판매수량
from 판매계획 a,
판매실적 b
where a.상품 = b.상품
and a.계획연월 = b.판매연월;
- 조인결과
| 상품 | 연월 | 계획수량 | 판매수량 |
|---|---|---|---|
| 기타 | 20120101 | 100 | 200 |
| 기타 | 20120101 | 100 | 200 |
| 기타 | 20120101 | 100 | 200 |
| 기타 | 20120102 | 300 | 100 |
| 첼로 | 20120104 | 400 | 400 |
| 첼로 | 20120104 | 400 | 400 |
| 첼로 | 20120104 | 400 | 400 |
| 첼로 | 20120104 | 400 | 400 |
| 피아노 | 20120121 | 100 | 100 |
| 피아노 | 20120124 | 100 | 100 |
| 오르간 | 20120122 | 300 | 300 |
| 오르간 | 20120122 | 300 | 300 |
| 오르간 | 20120122 | 300 | 300 |
| 오르간 | 20120122 | 300 | 300 |
| 기타 | 20120120 | 100 | 100 |
다. Outer Join을 활용한 문제해결
테이블의 잘못된값들은 정리하지 않고 SQL만 변경하여 문제해결을 하는 방법중 하나입니다. 판매계획, 판매실적 테이블의 값들을 GROUP BY하여 M개의 데이타를 1개로 압축한다. 그러면 두 테이블간의 관계가 1 대 1관계가 됩니다. 그런후 OUTER조인을 이용하여 데이타를 연결하면 됩니다.
select nvl(a.상품, b.상품) as 상품 ,
nvl(a.계획연월, b.판매연월) as 연월 ,
nvl(계획수량, 0) 계획수량 ,
nvl(판매수량, 0) 판매수량
from (select 상품, 계획연월, sum(계획수량) 계획수량
from 판매계획
where 계획연월 between '20120101' and '201231'
group by 상품, 계획연월 ) a
full outer join
(select 상품, 판매연월, sum(판매수량) 판매수량
from 판매실적
where 판매연월 between '20120101' and '20120331'
group by 상품, 판매연월 ) b
on a.상품 = b.상품
and a.계획연월 = b.판매연월
order by 1,2;
라. Union ALL을 활용한 문제해결
역시 테이블의 데이타는 정리하지 않고 2개의 테이블을 UNION ALL을 이용하여 논리적으로 2개의 테이블을 1개의 테이블로 만든다. 그런후 바깥단에서 GROUP BY하여 데이타를 수직으로 압축하면 됩니다.
select 상품, 연월, nvl(sum(계획수량), 0) as 계획수량,
nvl(sum(실적수량), 0) as 실적수량
from(select 상품, 계획연월 as 연월, 계획수량,
to_number(null) as 실적수량
from 판매계획
where 계획연월 between '200120101' and '20120331'
union all
select 상품, 판매연월 as 연월,
to_number(null) as 계획수량, 판매수량
from 판매실적
where 판매연월 between '200120101' and '20120331' ) a
group by 상품, 연월
order by 상품, 연월;
마. 클린징작업과 테이블 관계 재설정
정상적으로 조인문장을 사용하고 싶으면 데이타를 테이블관계에 맞게끔 클린징을 하면됩니다. 말은 쉽지만 상당한 고숙련도의 작업이 필요합니다. 데이타가 적은경우에는 좀 쉽게 할수 있겠지만, 수억건 이상 데이타를 가진 테이블이거나, 사용도가 매우 높거나 중요한 테이블이라면 작업이 쉽지 않을것으로 생각됩니다. 경우에 따라서는 시스템을 다운하고나서 사용자가 없을때 작업을 하여야 할수도 있습니다.
2. 결언
부모-자식간 테이블관계는 RDBMS에서 매우 중요한 이야기입니다. 이 관계가 깨지면 일잔적인 조인 SQL에서는 잘못된 값을 만나게 되는것입니다. 그렇다고 조인이 무조건 나쁜건 아니며 RDBMS는 조인이 생명입니다. 절대 오해없기를 바란다. 단지 이 칼럼에서는 흔히 발생하는 문제를 SQL형태만 수정하여 해결한 예를 든것입니다. 업무요구 사항이나 하드웨어/소프트웨어 스펙에 따라 SQL형태를 맞춰줘야 한다. 이 이야기는 계속 앞으로의 칼럼을 통해 계속하도록 하겟습니다.
부모-자식간 테이블관계는 RDBMS에서 매우 중요한 것입니다. 이 관계가 깨진경우(M-1, M-M)는 일반적인 조인 SQL에서는 잘못된 값을 만나게 되는것입니다. 그렇다고 조인이 무조건 나쁜것은 아니며 RDBMS는 조인이 생명입니다. 절대 오해없기를 바란다. 단지 이 칼럼에서는 흔히 발생하는 문제를 SQL형태만 수정하여 해결한 예를 든것입니다. 업무요구 사항이나 하드웨어/소프트웨어 스펙에 따라 SQL형태를 맞춰줘야 한다. 이 이야기는 계속 앞으로의 칼럼을 통해 계속하도록 하겟습니다.