프로그래머스 SQL 풀이 오답노트(8): JOIN, WITH

https://school.programmers.co.kr/learn/courses/30/lessons/157339

1. 틀린 쿼리

WITH TF AS (
    SELECT CC.CAR_ID AS CAR_ID, 
           CC.CAR_TYPE AS CAR_TYPE,
            (CC.DAILY_FEE*30*(100-CP.DISCOUNT_RATE)/100) AS TOTAL_FEE
    FROM CAR_RENTAL_COMPANY_CAR CC
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CP
    ON CC.CAR_TYPE = CP.CAR_TYPE
    WHERE (CP.DURATION_TYPE = '30일 이상')
    AND ((CC.CAR_TYPE = '세단') OR (CC.CAR_TYPE = 'SUV'))
),
RT AS (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE (START_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD') AND START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')) OR (END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD') AND END_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD'))
)
SELECT DISTINCT TF.CAR_ID, TF.CAR_TYPE, TF.TOTAL_FEE
FROM TF, RT
WHERE TF.CAR_ID NOT IN RT.CAR_ID
AND (TF.TOTAL_FEE >= 500000 AND TF.TOTAL_FEE < 2000000)
ORDER BY TF.TOTAL_FEE DESC, TF.CAR_TYPE, TF.CAR_ID DESC;

2. 정답쿼리

WITH TF AS (
    SELECT CC.CAR_ID AS CAR_ID, 
           CC.CAR_TYPE AS CAR_TYPE,
            (CC.DAILY_FEE*30*(100-CP.DISCOUNT_RATE)/100) AS TOTAL_FEE
    FROM CAR_RENTAL_COMPANY_CAR CC
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CP
    ON CC.CAR_TYPE = CP.CAR_TYPE
    WHERE (CP.DURATION_TYPE = '30일 이상')
    AND ((CC.CAR_TYPE = '세단') OR (CC.CAR_TYPE = 'SUV'))
),
RT AS (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
    AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
)
SELECT DISTINCT TF.CAR_ID, TF.CAR_TYPE, TF.TOTAL_FEE
FROM TF
WHERE TF.CAR_ID NOT IN (SELECT CAR_ID FROM RT)
AND (TF.TOTAL_FEE >= 500000 AND TF.TOTAL_FEE < 2000000)
ORDER BY TF.TOTAL_FEE DESC, TF.CAR_TYPE, TF.CAR_ID DESC;

3. 오답노트

기간 체크 오류

틀린 쿼리에서 기간을 잘못 고려함. 시작일이나 종료일이 11월에 있는 경우 말고 11월을 걸쳐서 대여되는 경우도 포함해야 하기 때문

즉, 아래처럼 하면 안되고

WHERE (START_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD') 
AND START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')) 
OR (END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD') 
AND END_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD'))
)

아래처럼 해야 함.

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')

NOT IN 잘못 사용

FROM TF, RT
WHERE TF.CAR_ID NOT IN RT.CAR_ID

위와 같은 방식은 아래와 같은 문제가 있음

CROSS JOIN 사용:
FROM TF, RT로 인해 TF와 RT의 모든 행이 조합되어버림.
예를 들어 RT에 5개의 대여 중인 차량이 있다면, TF의 각 차량이 5번씩 중복 생성됨.

잘못된 필터링 조건:
WHERE TF.CAR_ID NOT IN RT.CAR_ID는 각 조인된 행의 RT.CAR_ID 값과만 비교함.
즉, RT에 해당 차량이 단 한 번이라도 존재하지 않으면 조건을 만족하는 것으로 처리되어버림.
이는 RT에 해당 차량이 존재하더라도 다른 행과 조인될 때 포함될 수 있음을 의미함.

그니까 RT에 CAR_ID 1이 있더라도, 다른 행과 조인될 때 CAR_ID 1이 아닌 경우가 있으면 잘못 포함되어 버리는 것

따라서 아래와 같이 사용하여야 함

FROM TF
WHERE TF.CAR_ID NOT IN (SELECT CAR_ID FROM RT)

서브쿼리 사용:
NOT IN (SELECT CAR_ID FROM RT)의 경우 RT의 모든 CAR_ID를 먼저 집계하여, TF의 CAR_ID가 그 목록에 전혀 포함되지 않는 경우만 필터링하기에,
RT에 차량이 존재하는지 전체적으로 확인하기에 적합함.
(카타시안 곱도 아니라서 효율적)

---

4. 장원급제 쿼리

WITH CAR_DISCOUNT AS (
    SELECT 
        C.CAR_ID,
        C.CAR_TYPE,
        C.DAILY_FEE * 30 * (100 - D.DISCOUNT_RATE)/100 AS FEE_30DAY
    FROM CAR_RENTAL_COMPANY_CAR C
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
      ON C.CAR_TYPE = D.CAR_TYPE
     AND D.DURATION_TYPE = '30일 이상'
    WHERE C.CAR_TYPE IN ('세단', 'SUV')
), RENTED_CARS AS (
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE >= DATE '2022-11-01'
      AND START_DATE <= DATE '2022-11-30'
)
SELECT 
    CAR_ID,
    CAR_TYPE,
    FEE_30DAY AS TOTAL_FEE
FROM CAR_DISCOUNT
WHERE CAR_ID NOT IN (SELECT CAR_ID FROM RENTED_CARS)
  AND FEE_30DAY BETWEEN 500000 AND 1999999
ORDER BY 
    TOTAL_FEE DESC,
    CAR_TYPE ASC,
    CAR_ID DESC;

아... DATE랑 BETWEEN 쓸걸...