https://school.programmers.co.kr/learn/courses/30/lessons/157340
1. 실수모음
1.1 오라클에서 문자열을 큰 따옴표로 감쌈.
SELECT X.CAR_ID
FROM (
SELECT
CAR_ID,
CASE
WHEN ((DATE "2022-10-16" >= START_DATE) AND (DATE "2022-10-16" <= END_DATE)) THEN "대여중"
ELSE "대여 가능"
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) X- 위에 쿼리는 틀림.
- 다른건 차치하고서라도, Oracle SQL에서 문자열은 작은따옴표
'로 감싸야 함. - 큰 따옴표는 식별자(테이블 이름, 컬럼 이름 등)을 감싸는 데 사용됨
1.2 Having 절은 집계 함수 결과를 필터링하는 데 사용 됨.
- 따라서 개별 행에 대한 조건은
WHERE절에서 처리해야 함. =<이런거 잘못된 연산자임.<=이게 맞음- 적절한 HAVING 절 예시
HAVING COUNT(*) > 1그룹 내에서 행의 개수가 1보다 큰 경우만 선택함.
2. 정답 쿼리
WITH CANT_AVAI AS (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE '2022-10-16' <= END_DATE
AND DATE '2022-10-16' >= START_DATE
GROUP BY CAR_ID
),
CAR_ID_LIST AS (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
SELECT CAR_ID,
CASE
WHEN CAR_ID NOT IN (SELECT CAR_ID FROM CANT_AVAI) THEN '대여 가능'
ELSE '대여중'
END AS AVAILABILITY
FROM CAR_ID_LIST
ORDER BY CAR_ID DESC3. 최적화 포인트
나쁘지 않은 쿼리이지만, NOT IN의 경우에 서브 쿼리 결과 안에 NULL이 포함되어 있으면 예상하지 못한 결과를 반환할 수도 있음.
따라서 NOT EXISTS를 사용하여 NULL을 안전하게 처리하는 것이 좋음.
다음과 같은 최적화 포인트가 있다고 볼 수 있음
1. 불필요 WITH절 제거
다만, 모든 WITH절이 불필요한 것은 아님. 적절한 WITH절은 가시성도 좋고 필요함
2. EXISTS 절 사용
EXISTS는 조건에 맞는 행이 있는 경우 바로 종료됨. 인덱스 구성이 잘 되어있는 경우일 수록 더 좋음
뿐더러 NOT IN과 달리 NULL 값 문제를 회피 가능
최적화 쿼리
SELECT c.CAR_ID,
CASE
WHEN EXISTS (
SELECT 1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
WHERE h.CAR_ID = c.CAR_ID
AND DATE '2022-10-16' BETWEEN h.START_DATE AND h.END_DATE
) THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) c
ORDER BY c.CAR_ID DESC;
EXISTS 절 내부 (Inner Query)
EXISTS는 내부 쿼리가 하나라도 행을 반환하면 TRUE로 평가됨.
이 쿼리에서 EXISTS 쿼리의 역할은, 현재 외부 쿼리에서 선택한 c.CAR_ID와 같은 차량이 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 존재하면서, 지정한 날짜(2022-10-16)가 그 차량의 대여 기간(h.START_DATE와 h.END_DATE) 안에 포함되어 있는지 확인하는 역할.
구체적으로,
WHERE h.CAR_ID = c.CAR_ID
이 조건은 외부 쿼리의 c.CAR_ID와 내부 쿼리의 h.CAR_ID를 연결(연관)하는 역할을 함.
이것이 바로 "연결다리(코릴레이션 조건)" 역할
AND DATE '2022-10-16' BETWEEN h.START_DATE AND h.END_DATE
이 조건은 주어진 날짜가 해당 대여 기록의 기간 안에 있는지를 확인
동작 과정 예시
외부 쿼리에서 하나의 CAR_ID를 선택
그 CAR_ID에 대해 내부 쿼리가 실행되며,
"해당 차량의 대여 기록 중 하나라도 2022-10-16이 대여 기간 안에 있으면"
내부 쿼리는 결과를 반환
내부 쿼리가 하나라도 결과(row)를 반환하면 EXISTS는 TRUE가 되어 CASE 구문에서 '대여중'을 선택
만약 조건에 맞는 대여 기록이 하나도 없다면, EXISTS는 FALSE가 되어 CASE 구문에서 '대여 가능'을 반환
연결다리 역할
연결다리 역할은 외부 쿼리의 c.CAR_ID와 내부 쿼리의 h.CAR_ID를 매칭시켜 주는 조건 h.CAR_ID = c.CAR_ID
이 조건으로 인하여 각 차량마다 해당 차량의 대여 이력을 내부 쿼리에서 검색할 수 있게 됨.
4. MS SQL SERVER 기준 쿼리
DATE만 빼면 동일
WITH CANT_AVAI AS (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE '2022-10-16' <= END_DATE -- MS SQL에서는 DATE 표기 불필요
AND '2022-10-16' >= START_DATE
GROUP BY CAR_ID
),
CAR_ID_LIST AS (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
SELECT CAR_ID,
CASE
WHEN CAR_ID NOT IN (SELECT CAR_ID FROM CANT_AVAI) THEN '대여 가능'
ELSE '대여중'
END AS AVAILABILITY
FROM CAR_ID_LIST
ORDER BY CAR_ID DESC
-- 더 나은 쿼리
SELECT c.CAR_ID,
CASE
WHEN EXISTS (
SELECT 1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
WHERE h.CAR_ID = c.CAR_ID
AND '2022-10-16' BETWEEN h.START_DATE AND h.END_DATE
) THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) c
ORDER BY c.CAR_ID DESC;
-- DATE 변환이 더 맘이 편하다면 아래처럼 하면 됨.
SELECT c.CAR_ID,
CASE
WHEN EXISTS (
SELECT 1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
WHERE h.CAR_ID = c.CAR_ID
AND CAST('2022-10-16' AS DATE) BETWEEN h.START_DATE AND h.END_DATE
) THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) c
ORDER BY c.CAR_ID DESC;CAST는 ANSI SQL 표준 함수이기에 데이터베이스 이식성이 좋음.
