Oracle BETWEEN 으로 기간끼리 겹치는 데이터를 찾을 때 빠지는 함정

결론부터. 데이터가 기간(시작일~종료일)이고 조회 조건도 기간이면, 두 기간이 겹치는지는 아래 두 줄이면 된다.

WHERE A.START_DT <= :TO_DT     -- 데이터 시작일 <= 조회 종료일
  AND A.END_DT   >= :FROM_DT   -- 데이터 종료일 >= 조회 시작일

BETWEEN 을 두 번 쓰는 방식은 그럴듯해 보이지만 빠지는 경우가 있다. 아래에서 차례로 본다.

데이터가 하루짜리면 BETWEEN 하나로 충분하다

데이터에 일자가 하나만 있고 화면 조회 조건이 FROM~TO 라면 BETWEEN 하나로 끝난다. BETWEEN a AND b 는 >= a AND <= b 와 같다(양 끝 포함).

WITH T AS (
    SELECT '20220711' AS DT FROM DUAL
)
SELECT A.DT
  FROM T A
 WHERE A.DT BETWEEN '20220708' AND '20220712';
-- 결과: 20220711

BETWEEN 은 앞 값이 뒤 값보다 작거나 같아야 한다. BETWEEN '20220712' AND '20220708' 처럼 순서가 뒤집히면 오류 없이 0건이 나오니, 화면에서 FROM·TO 를 거꾸로 넣는 경우를 막아 두는 편이 좋다.

데이터도 기간이면: BETWEEN 두 번은 빠지는 행이 있다

이번엔 데이터가 7월 11일~17일인 기간이다. 처음에는 “시작일이 조회 기간 안에 있거나, 종료일이 조회 기간 안에 있으면 겹친다”고 생각했다.

WITH T AS (
    SELECT '20220711' AS START_DT, '20220717' AS END_DT FROM DUAL
)
SELECT A.START_DT, A.END_DT
  FROM T A
 WHERE (   A.START_DT BETWEEN '20220708' AND '20220712'
        OR A.END_DT   BETWEEN '20220708' AND '20220712');
-- 결과: 20220711 / 20220717

조회 기간을 7월 8일~~12일로 주면 잘 나온다. 그런데 조회 기간이 데이터 기간 안쪽(13일~~15일)이면 나오지 않는다. 시작일도 종료일도 조회 기간 밖이기 때문이다.

 WHERE (   A.START_DT BETWEEN '20220713' AND '20220715'
        OR A.END_DT   BETWEEN '20220713' AND '20220715');
-- 결과: 0건 (실제로는 겹친다)

데이터 기간(파랑)과 조회 기간(주황)이 겹치는 경우는 네 가지다.

데이터 기간 11~17일과 겹치는 조회 조건 네 가지: 앞쪽에 걸침, 뒤쪽에 걸침, 안쪽에 포함, 바깥에서 감쌈

BETWEEN 두 번은 조회 조건 1(앞쪽에 걸침)과 2(뒤쪽에 걸침)만 잡는다. 조건 3(데이터 기간 안쪽)과 4(데이터 기간을 감쌈)까지 잡으려면 이렇게 네 개가 된다.

 WHERE (   A.START_DT BETWEEN '20220713' AND '20220715'
        OR A.END_DT   BETWEEN '20220713' AND '20220715'
        OR '20220713' BETWEEN A.START_DT AND A.END_DT
        OR '20220715' BETWEEN A.START_DT AND A.END_DT);

결과는 맞지만 읽기 어렵고, OR 가 넷이라 인덱스도 쓰기 어렵다.

해결: “겹치지 않는 경우”를 뒤집는다

두 기간이 겹치지 않는 경우는 딱 둘이다. 데이터가 조회 기간보다 완전히 앞에 있거나(데이터 종료일 < 조회 시작일), 완전히 뒤에 있거나(데이터 시작일 > 조회 종료일). 이 둘을 뒤집으면 겹침 조건이 된다.

WITH T AS (
    SELECT '20220711' AS START_DT, '20220717' AS END_DT FROM DUAL
)
SELECT A.START_DT, A.END_DT
  FROM T A
 WHERE A.START_DT <= '20220715'   -- 데이터 시작일 <= 조회 종료일
   AND A.END_DT   >= '20220713';  -- 데이터 종료일 >= 조회 시작일

위 그림의 네 가지 경우를 모두 잡고, 조건이 AND 두 개라 START_DT 나 END_DT 인덱스로 범위를 좁힐 수 있다.

종료일이 비어 있는(아직 끝나지 않은) 데이터가 있다면 NULL 을 따로 다뤄야 한다. NULL 과의 비교는 참이 아니라서 그냥 두면 빠진다.

 WHERE A.START_DT <= :TO_DT
   AND (A.END_DT >= :FROM_DT OR A.END_DT IS NULL)

일자를 문자열로 둘 때

예제처럼 'YYYYMMDD' 문자열로 저장한 일자는 문자열 비교로도 순서가 맞는다. 단 모든 값이 같은 길이·같은 형식일 때만 그렇다. '2022711' 같은 값이 섞이면 결과가 틀어진다.

또 VARCHAR2 일자 컬럼을 DATE 값과 비교하면 Oracle 이 컬럼 쪽에 TO_DATE 를 씌워 변환하므로 그 컬럼의 인덱스를 못 쓴다. 비교 값을 컬럼과 같은 형식의 문자열로 맞춘다.

DATE·TIMESTAMP 에 BETWEEN 을 쓸 때

DATE 는 이름과 달리 시·분·초까지 들고 있다. 그래서 아래 조건은 7월 12일 데이터 대부분을 놓친다.

-- 7월 12일 00:00:00 까지만 포함된다. 12일 09:30 은 빠진다
WHERE ORDER_DT BETWEEN DATE '2022-07-08' AND DATE '2022-07-12'

끝 날짜는 “다음 날 0시 미만”으로 쓰는 것이 안전하다. TIMESTAMP 도 똑같다.

WHERE ORDER_DT >= DATE '2022-07-08'
  AND ORDER_DT <  DATE '2022-07-12' + 1

TRUNC(ORDER_DT) BETWEEN ... 으로 써도 결과는 맞지만, 컬럼에 함수를 씌웠으므로 ORDER_DT 일반 인덱스를 쓰지 못한다(같은 식의 함수 기반 인덱스가 있으면 예외).

기간 겹침 조건도 시각이 있는 컬럼이라면 같은 방식으로 쓴다.

WHERE A.START_DT <  :TO_DT + 1   -- 조회 종료일 다음 날 0시 미만
  AND A.END_DT   >= :FROM_DT

정리

하루짜리 데이터 + 기간 조회: BETWEEN 하나
기간 데이터 + 기간 조회: 시작일 <= 조회 종료일 AND 종료일 >= 조회 시작일
종료일 NULL(진행 중)은 따로 처리
DATE·TIMESTAMP 는 끝 날짜를 < 다음 날 로

다시 쓰며 바로잡은 점

원문은 마지막 해법을 “이렇게도 할 수 있다” 정도로 소개했는데, 실제로는 이 두 줄 조건이 네 경우를 모두 잡는 정답이고 인덱스에도 유리해서 맨 앞으로 올렸다.
종료일 NULL, 문자열 일자의 형식 조건, DATE·TIMESTAMP 의 시각 부분 문제를 덧붙였다.