MST - 마스터 테이블, DTL - 디테일 테이블이 있습니다.
MST 테이블 데이터
BPTM_TR_NO BPTM_TR_DT
20170816001 2017-08-16
20170816002 2017-08-16
20170816003 2017-08-16
DTL 테이블 데이터
BPTD_TR_NO IN_MDESC
20170816002 A
20170816002 B
20170816002 C
20170816002 D
20170816002 E
20170816002 F
20170816001 AA
20170816002 G
select
a.BPTM_TR_NO, a.BPTM_TR_DT
from mst a, dtl b
where
a.BPTM_TR_NO = b.BPTD_TR_NO(+)
and a.BPTM_TR_NO like '%%'
and b.IN_MDESC like '%%'
group by BPTM_TR_NO, BPTM_TR_DT;
위 처럼 쿼리문을 실행하였을 때 제가 원하는 데이터는 아래와 같이 나오는거 입니다.
20170816001 2017-08-16
20170816002 2017-08-16
20170816003 2017-08-16
그런데, 20170816003 데이터의 DTL 테이블의 데이터가 없어서 출력이 안됩니다.
조언 좀 부탁드립니다.
감사합니다.
WITH T AS (
select 'a' code, '1' val from dual union all
select 'b' code, '1' val from dual union all
select 'c' code, '1' val from dual
), SUB AS (
select 'a' code, '11' val from dual union all
select 'a' code, '11' val from dual union all
select 'a' code, '11' val from dual union all
select 'a' code, '11' val from dual union all
select 'a' code, '11' val from dual union all
select 'b' code, '11' val from dual union all
select 'b' code, '11' val from dual union all
select 'b' code, '11' val from dual
)
SELECT A.code
,A.val
,sum(b.val) b_val
FROM T A
,SUB B
WHERE A.code = B.code(+)
GROUP BY
A.code
,A.val
order by
a.code
,a.val
;
조인 조건에는 아우터를 걸었으나. AND a.bptm_tr_no = b.bptd_tr_no(+)
검색 조건에는 아우터를 안걸었네요. AND b.in_mdesc LIKE '%%'
이러면 아우터 조인이 무효화 됩니다. 이너조인이나 마찬가지.
1. 검색 조건 쪽에도 (+) 기호를 붙여줘야 합니다.
- 변경전 : AND b.in_mdesc LIKE '%%'
- 변경후 : AND b.in_mdesc(+) LIKE '%%'
2. group by 구문에 a. 이 누락되어 에러날 듯 하구요.
- 변경전 : GROUP BY bptm_tr_no, bptm_tr_dt
- 변경후 : GROUP BY a.bptm_tr_no, a.bptm_tr_dt
- 다시 보니 컬럼명이 달라서 에러는 안나겠네요.
- 그래도 알리아스 붙여주는게 좋을 듯 합니다.
3. b를 조인 했지만? b 의 항목을 사용하지 않네요?
- 조인은 왜 한거죠?
- 그냥 a 만 조회하면 될 듯 하네요?
- 혹시 질문을 간략하게 하기 위해 b 의 조회 부분을 뺀거라면?
- 질문을 외곡시켰기 때문에 헷갈릴 수 있습니다.
- 만약 질문을 위해 쿼리를 간략하게 바꾼게 아니라 실제 쿼리를 올리신거라면?
- 조인은 전혀 필요 없습니다. 그냥 a만 조회하면 됩니다.
SELECT * FROM MST A WHERE BPTM_TR_NO = NVL(:IN_BPTM_TR_NO, BPTM_TR_NO) AND :IN_MDESC IS NOT NULL AND EXISTS (SELECT 1 FROM DTL B WHERE B.BPTD_TR_NO = A.BPTM_TR_NO AND IN_MDESC LIKE '%' || :IN_MDESC || '%') UNION ALL SELECT * FROM MST A WHERE BPTM_TR_NO = NVL(:IN_BPTM_TR_NO, BPTM_TR_NO) AND :IN_MDESC IS NULL
MST의 BPTM_TR_NO 컬럼에 NULL 값 없죠?
인덱스 구성이나 데이터량 등에 따라 어떤게 유리할지 모르겠네요.
1. in_mdesc 로 인덱스 검색이 가능한 경우라면?
- in_mdesc 가 선두컬럼인 인덱스 존재할 때
- (in_mdesc, bptd_tr_no) 또는 아쉬운 대로 (in_mdesc)
- 조인으로 푸는게 유리함
- UNION ALL 을 이용해 조건 입력에 따라 쿼리 분리
2. in_mdesc 로 인덱스 검색이 불가능한 경우라면?
- in_mdesc 가 선두컬럼인 인덱스가 존재하지 않을 때
- dtl 의 건수가 mst 에 비해 상대적으로 많을 경우
- (bptd_tr_no, in_mdesc) 또는 아쉽지만 (bptd_tr_no) 인덱스 필요
- EXISTS
-- 1. Union All 과 Join 으로 쿼리 분리 SELECT a.bptm_tr_no, a.bptm_tr_dt FROM mst a WHERE :in_value IS NULL UNION ALL SELECT a.bptm_tr_no, a.bptm_tr_dt FROM mst a , dtl b WHERE a.bptm_tr_no = b.bptd_tr_no AND b.in_mdesc = :in_value AND :in_value IS NOT NULL ;
-- 2. OR 절과 Exists SELECT a.bptm_tr_no , a.bptm_tr_dt FROM mst a WHERE :in_value IS NULL OR EXISTS (SELECT 1 FROM dtl b WHERE b.bptd_tr_no = a.bptm_tr_no AND b.in_mdesc = :in_value AND :in_value IS NOT NULL ) ;
답변 달고 나서 보니..
a 에도 조건이 있었네요....
a 조건 추가요.
-- 1. Union All 과 Join 으로 쿼리 분리 SELECT a.bptm_tr_no , a.bptm_tr_dt FROM mst a WHERE :in_value IS NULL AND a.bptm_tr_no = NVL(:in_bptm_tr_no, a.bptm_tr_no) UNION ALL SELECT a.bptm_tr_no, a.bptm_tr_dt FROM mst a , dtl b WHERE a.bptm_tr_no = b.bptd_tr_no AND a.bptm_tr_no = NVL(:in_bptm_tr_no, a.bptm_tr_no) AND b.in_mdesc = :in_value AND :in_value IS NOT NULL ; -- 2. OR 절과 Exists SELECT a.bptm_tr_no , a.bptm_tr_dt FROM mst a WHERE a.bptm_tr_no = NVL(:in_bptm_tr_no, a.bptm_tr_no) AND (EXISTS (SELECT 1 FROM dtl b WHERE b.bptd_tr_no = a.bptm_tr_no AND b.in_mdesc = :in_value AND :in_value IS NOT NULL ) OR :in_value IS NULL ) ;