2021-05-25-TIL

2021-05-25-TIL

숙소 검색 필터를 SQL로 옮겼다. 조건을 하나씩 늘려 가며 쿼리를 확인했다.

---

날짜가 겹치는 예약 찾기

• location, checkIn, checkOut 필터 검색

  location은 ‘서울’이면서 checkIn 날짜가 ‘2021-08-01’과 ‘2021-08-30’사이에 예약된 정보가 없는 방을 조회

SELECT * FROM room WHERE location = '서울' AND id
NOT IN
(SELECT room_id FROM reserve WHERE check_in < '2021-08-30' AND check_out > '2021-08-01');

http://www.gurubee.net/article/65624

서브쿼리의 조건이 핵심이다. 기존 예약 기간 [check_in, check_out)과 요청 기간 [checkIn, checkOut)이 겹치려면 “기존 예약이 요청 종료일보다 먼저 시작하고, 요청 시작일보다 늦게 끝나야” 한다.

check_in < 요청 checkOut  AND  check_out > 요청 checkIn

반대로 겹치지 않는 경우는 기존 예약이 요청 시작 전에 끝나거나(check_out <= checkIn), 요청 종료 후에 시작하는(check_in >= checkOut) 두 가지뿐이다. 두 조건을 부정해서 합치면 위 식이 나온다. 부등호에 =를 넣지 않았기 때문에 체크아웃 날짜에 다른 손님이 체크인하는 경우는 겹침으로 보지 않는다. 이 조건에 걸리는 방 id를 모은 뒤 NOT IN으로 빼면 예약 가능한 방이 남는다.

NOT IN에는 함정이 하나 있다. MySQL은 SQL 표준에 따라, 목록에 일치하는 값이 없고 목록 안에 NULL이 있으면 IN()이 NULL을 돌려준다. NOT IN은 그 부정이라 역시 NULL이 되고, WHERE에서 NULL은 거짓처럼 처리되어 행이 하나도 나오지 않는다(MySQL 비교 연산자). reserve.room_id는 NOT NULL로 두거나, NOT EXISTS로 바꾸면 이 문제를 피할 수 있다.

---

가격 조건

• location, checkIn, checkOut, max, min 필터 검색

  location은 ‘서울’이면서 checkIn 날짜가 ‘2021-08-01’과 ‘2021-08-30’사이에 예약된 정보가 없고, 가격은 max와 min 사이에 있는 방을 조회

SELECT * FROM room WHERE location = '서울' 
AND id NOT IN
(SELECT room_id FROM reserve WHERE check_in < '2021-08-30' AND check_out > '2021-08-01')
AND (charge BETWEEN 80000 AND 90000);

BETWEEN min AND max는 min <= charge AND charge <= max와 같아서 양 끝 값을 포함한다(MySQL 비교 연산자).

---

인원 조건

• location, checkIn, checkOut, max, min, guests 필터 검색

  location은 ‘서울’이면서 checkIn 날짜가 ‘2021-08-01’과 ‘2021-08-30’사이에 예약된 정보가 없고, 가격은 max와 min 사이에 있고, 수용인원이 3명 이하인 방을 조회

SELECT * FROM room WHERE location = '서울' 
AND id NOT IN
(SELECT room_id FROM reserve WHERE check_in < '2021-08-30' AND check_out > '2021-08-01')
AND (charge BETWEEN 80000 AND 90000)
AND guests <= 3;

여기서 guests 컬럼이 방의 최대 수용 인원이라면, “3명이 묵을 수 있는 방”을 찾는 조건은 guests >= 3이어야 한다. guests <= 3은 최대 수용 인원이 3명 이하인 방, 즉 작은 방을 고른다. 쿼리를 코드로 옮길 때 컬럼의 의미를 기준으로 부등호 방향을 다시 확인해야 한다.

id	password	nickname	email
august	1234	Augboot	august@gmail.com
lukeaugust	1234	Luke	luke@gmail.comaugust@gmail.com
ray	1234	Ray	ray@naver.com
team10	1010	Ship	ship@codesquad.com

참고한 자료외부 출처 1

외부 출처

• Comparison Functions and OperatorsMySQL 8.0 Reference Manual