SELECT ROWNUM
, A.CONTENTS_ID
, A.TITLE
, A.LANG_CD
, A.UPDATE_DATE
FROM (
SELECT B.CONTENTS_ID
, B.TITLE
, B.DESCRIPTION
, B.LANG_CD
, B.UPDATE_DATE
FROM TB_CONTENTS B
WHERE 1=1
AND B.CONTENTS_TYPE = 'FOT'
AND B.USE_YN = 'Y'
AND B.DEL_YN = 'N'
AND B.POC_CD = 'NO'
AND B.LANG_CD = 'KO'
ORDER BY B.UPDATE_DATE ASC
) A
ORDER BY ROWNUM DESC
;
위 쿼리를 돌리면 아래 사진과 같이 4개의 데이터가 나오는데요
SELECT ROWNUM
, A.CONTENTS_ID
, A.TITLE
, A.LANG_CD
, A.UPDATE_DATE
, A.COMP_TEXT
, A.COMP_TYPE
FROM (
SELECT B.CONTENTS_ID
, B.TITLE
, B.DESCRIPTION
, B.LANG_CD
, B.UPDATE_DATE
, C.COMP_TEXT
, C.COMP_TYPE
, ROW_NUMBER() OVER(PARTITION BY B.CONTENTS_ID ORDER BY C.SORT_NO ASC) AS SORT_NO
FROM TB_CONTENTS B, TB_CONT_COMPONENT C
WHERE 1=1
AND B.CONTENTS_ID = C.CONTENTS_ID
AND B.CONTENTS_TYPE = 'FOT'
AND B.USE_YN = 'Y'
AND B.DEL_YN = 'N'
AND B.POC_CD = 'NO'
AND B.LANG_CD = 'KO'
AND C.COMP_TYPE = 'A3'
AND C.LANG_CD = 'KO'
ORDER BY B.UPDATE_DATE ASC
) A
WHERE SORT_NO = 1
ORDER BY ROWNUM DESC
;
이 쿼리를 돌리면 3개의 데이터가 나옵니다
왜냐하면 FOT00000000000001009에는 WHERE절 조건에서 준 AND C.COMP_TYPE = 'A3' 부분에서
A3가 포함되지 않기 때문입니다.
그런데 A3라는 조건을 줬어도 없으면 NULL로 나오게 할 수는 없을까요?
-- Outer Join (+) -- SELECT ROWNUM rn , a.contents_id , a.title , a.lang_cd , a.update_date , a.comp_text , a.comp_type FROM (SELECT b.contents_id , b.title , b.description , b.lang_cd , b.update_date , c.comp_text , c.comp_type , ROW_NUMBER() OVER(PARTITION BY b.contents_id ORDER BY c.sort_no) sort_no FROM tb_contents b , tb_cont_component c WHERE 1=1 AND b.contents_id = c.contents_id(+) AND b.contents_type = 'FOT' AND b.use_yn = 'Y' AND b.del_yn = 'N' AND b.poc_cd = 'NO' AND b.lang_cd = 'KO' AND c.comp_type(+) = 'A3' AND c.lang_cd (+) = 'KO' ORDER BY b.update_date ) a WHERE sort_no = 1 ORDER BY ROWNUM DESC ;