[Oracle/SQL] 댓글 테이블과 JOIN 할 때는 OUTER JOIN 하기

댓글이 없을 때는 조회가 안 되기 때문!

그리고 LEFT 꼭 써주기! 안 쓰면 누락된 키워드 에러 뜬다.

 

테이블 쪼갰더니 쿼리 엄청 길다.

	<select id="getList" resultType="hashmap" parameterType="hashmap">
		SELECT M.MEM_NAME, R.RECIPE_TITLE, 
		        TO_CHAR(R.REG_DATE, 'YYYY-MM-DD HH24:MI:SS')AS "REG_DATE",
		        RC.CNT AS "CMT", RF.CNT AS "FAV", RL.CNT AS "LIKE",
		        RI.IMG
		FROM RECIPE R INNER JOIN MEMBER M
		                    ON M.MEM_NO = R.MEM_NO
		              INNER JOIN RECIPE_IMG RI
		                    ON R.RECIPE_NO = RI.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_LIKE
		                        WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RL
		                    ON R.RECIPE_NO = RL.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_CMT
		                        WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RC
		                    ON R.RECIPE_NO = RC.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_FAV
		                        WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RF
		                    ON R.RECIPE_NO = RF.RECIPE_NO
		WHERE R.DEL = 1
		AND RI.IMG_TYPE = 0
		<if test="searchText != null and searchText != ''">
			<choose>
				<when test="searchGbn eq 0">
					AND M.MEM_NAME LIKE '%' || #{searchText} || '%'
				</when>
				<when test="searchGbn eq 1">
					AND R.RECIPE_TITLE LIKE '%' || #{searchText} || '%'
				</when>
			</choose>
		</if>
	</select>

 

 

+ 표시할 때는 댓글 없는 건(null) 0으로 표시해야 하므로 NVL 사용하기!

	<select id="getList" resultType="hashmap" parameterType="hashmap">
		SELECT M.MEM_NAME, R.RECIPE_TITLE, 
		        TO_CHAR(R.REG_DATE, 'YYYY-MM-DD HH24:MI:SS')AS "REG_DATE",
		        NVL(RC.CNT, 0) AS "CMT", NVL(RF.CNT, 0) AS "FAV", 
		        NVL(RL.CNT, 0) AS "LIKE", RI.IMG
		FROM RECIPE R INNER JOIN MEMBER M
		                    ON M.MEM_NO = R.MEM_NO
		              INNER JOIN RECIPE_IMG RI
		                    ON R.RECIPE_NO = RI.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_LIKE
								WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RL
		                    ON R.RECIPE_NO = RL.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_CMT
		                        WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RC
		                    ON R.RECIPE_NO = RC.RECIPE_NO
		              LEFT OUTER JOIN (SELECT COUNT(*) AS CNT, RECIPE_NO, DEL
		                        FROM RECIPE_FAV
		                        WHERE DEL = 1
		                        GROUP BY RECIPE_NO, DEL)RF
		                    ON R.RECIPE_NO = RF.RECIPE_NO
		WHERE R.DEL = 1
		AND RI.IMG_TYPE = 0
		<if test="searchText != null and searchText != ''">
			<choose>
				<when test="searchGbn eq 0">
					AND M.MEM_NAME LIKE '%' || #{searchText} || '%'
				</when>
				<when test="searchGbn eq 1">
					AND R.RECIPE_TITLE LIKE '%' || #{searchText} || '%'
				</when>
			</choose>
		</if>
	</select>