소스: sql-src/work_03_history/02_change_overlap.sql, 03_dialects.sql.
남개발이 2026-10-01 에 인프라 과장이 됩니다. 두 문장을 한 트랜잭션으로 묶습니다.
-- 트랜잭션 시작 (MySQL: START TRANSACTION, MSSQL: BEGIN TRAN, Oracle 은 첫 DML 부터)
UPDATE emp_hist SET valid_to = DATE '2026-10-01' WHERE emp_id = 3 AND valid_to = DATE '9999-12-31';
INSERT INTO emp_hist VALUES (3, '인프라', '과장', DATE '2026-10-01', DATE '9999-12-31');
-- COMMIT;SELECT
emp_id
, dept
, grade
, valid_from
, valid_to
FROM emp_hist
WHERE emp_id = 3
ORDER BY valid_from;EMP_ID | DEPT | GRADE | VALID_FROM | VALID_TO
-------+--------+-------+------------+-----------
3 | 개발 | 사원 | 2022-07-01 | 2024-01-01
3 | 인프라 | 대리 | 2024-01-01 | 2026-10-01
3 | 인프라 | 과장 | 2026-10-01 | 9999-12-31
(3행)UPDATE 조건에 valid_to = 9999-12-31 이 들어가 이미 닫힌 행을 다시 건드리지 않습니다. 영향받은 행이 1 이 아니면 롤백하도록 애플리케이션에서 확인합니다.
정상 데이터에서는 0행이어야 합니다.
SELECT
a.emp_id
, a.grade AS a_grade
, b.grade AS b_grade
FROM emp_hist a
JOIN emp_hist b ON b.emp_id = a.emp_id
AND a.valid_from < b.valid_from
AND a.valid_from < b.valid_to
AND b.valid_from < a.valid_to;EMP_ID | A_GRADE | B_GRADE
-------+---------+--------
(0행)세 번째 조건 a.valid_from < b.valid_to 는 a.valid_from < b.valid_from 이 이미 참이면 사실상 항상 참입니다. 겹침 공식을 그대로 두어 읽기 쉽게 했습니다.
이 조건을 끝을 포함하는 <= 로 바꾸면 이어 붙은 정상 행 8쌍이 전부 오탐지됩니다(소스 파일의 COUNT(*) 쿼리 결과가 8). 이제 이영업에게 기간이 겹치는 잘못된 행 (4, 마케팅, 차장, 2025-02-01, 2025-06-01) 을 넣고 같은 검사를 다시 돌립니다.
SELECT
a.emp_id
, a.grade AS a_grade
, a.valid_from AS a_from
, a.valid_to AS a_to
, b.grade AS b_grade
, b.valid_from AS b_from
, b.valid_to AS b_to
FROM emp_hist a
JOIN emp_hist b ON b.emp_id = a.emp_id
AND a.valid_from < b.valid_from
AND a.valid_from < b.valid_to
AND b.valid_from < a.valid_to
ORDER BY a.emp_id, a.valid_from;EMP_ID | A_GRADE | A_FROM | A_TO | B_GRADE | B_FROM | B_TO
-------+---------+------------+------------+---------+------------+-----------
4 | 대리 | 2023-10-01 | 2025-04-01 | 차장 | 2025-02-01 | 2025-06-01
4 | 차장 | 2025-02-01 | 2025-06-01 | 과장 | 2025-04-01 | 9999-12-31
(2행)차장 행이 앞 행(대리)과 뒤 행(과장) 모두와 겹쳐 두 쌍이 나옵니다. 대리와 과장은 2025-04-01 에 맞닿아 있을 뿐이라 짝으로 잡히지 않습니다. 저장하기 전에는 새 행의 기간을 넣어 같은 조건으로 확인합니다.
SELECT
COUNT(*) AS overlap_cnt
FROM emp_hist
WHERE emp_id = 1
AND valid_from < DATE '2024-01-01'
AND DATE '2023-06-01' < valid_to;OVERLAP_CNT
-----------
1
(1행)김대표에게 [2023-06-01, 2024-01-01) 행을 넣으려는 상황이고, 기존 임원 행과 겹쳐 1 이 나오니 넣으면 안 됩니다. 0 일 때만 INSERT 합니다. 동시에 두 세션이 검사를 통과할 수 있으므로 사원 행을 먼저 잠그거나 트리거로 한 번 더 막습니다.
잘못된 차장 행을 지운 뒤, 김개발 대리 행의 valid_to 를 2025-08-01 로 잘못 고쳐 구멍을 만듭니다. LAG 로 이전 행의 끝을 가져와 이번 시작과 비교합니다.
SELECT
emp_id
, prev_to AS gap_from
, valid_from AS gap_to
FROM (
SELECT
emp_id
, valid_from
, LAG(valid_to) OVER (PARTITION BY emp_id ORDER BY valid_from) AS prev_to
FROM emp_hist
) t
WHERE prev_to < valid_from
ORDER BY emp_id;EMP_ID | GAP_FROM | GAP_TO
-------+------------+-----------
2 | 2025-08-01 | 2025-09-01
(1행)2025-08-01 부터 2025-09-01 직전까지 김개발의 부서·직급이 없는 기간이 나옵니다. 첫 행은 prev_to 가 NULL 이라 조건에서 빠집니다. 비교를 <> 로 하면 겹침도 같이 걸리고, < 로 하면 구멍만 걸립니다. 각 사원에 현재 행이 정확히 하나인지도 GROUP BY emp_id HAVING COUNT(*) <> 1 로 함께 봅니다.
소스 03_dialects.sql 은 SET MODE 로 세 방언의 날짜 리터럴을 실행합니다. 결과는 예제 1 의 2024-06-30 상태 4행과 같습니다.
-- Oracle · Tibero
WHERE valid_from <= TO_DATE('2024-06-30', 'YYYY-MM-DD')
AND TO_DATE('2024-06-30', 'YYYY-MM-DD') < valid_to-- MySQL: WHERE valid_from <= '2024-06-30' AND '2024-06-30' < valid_to
-- MSSQL: CAST('2024-06-30' AS DATE) 로 날짜 지정오늘 기준이면 Oracle 은 TRUNC(SYSDATE), MySQL 은 CURDATE(), MSSQL 은 CAST(GETDATE() AS DATE) 를 씁니다(중급 07 날짜 함수). Oracle SYSDATE 는 시각을 가지므로 TRUNC 로 날짜만 남깁니다.
다른 설계는 현재 행의 valid_to 를 NULL 로 두는 방식입니다. 9999-12-31 이라는 가짜 날짜가 없어 보이지만 조건이 복잡해집니다. 예제 데이터는 김개발의 과장 행과 남개발의 대리 행을 NULL 로 둔 emp_hist_n 입니다.
SELECT emp_id, grade FROM emp_hist_n
WHERE valid_from <= DATE '2026-01-01' AND DATE '2026-01-01' < valid_to;EMP_ID | GRADE
-------+------
(0행)d < NULL 은 참도 거짓도 아닌 알 수 없음이라 현재 행이 전부 사라집니다. valid_to IS NULL OR 를 붙이면 2행(김개발 과장, 남개발 대리)이 나오고, COALESCE(valid_to, DATE '9999-12-31') 로 감싸도 결과는 같습니다.
하지만 COALESCE 처럼 컬럼에 함수를 씌우면 valid_to 인덱스를 못 씁니다. Oracle 의 단일 컬럼 B-tree 인덱스는 NULL 을 저장하지 않아서 IS NULL 조건에도 쓸 수 없습니다. 9999-12-31 방식은 현재 행도 값을 가지므로 어느 DB 에서나 인덱스를 씁니다.
팁현재 행을 찾는 쿼리가 많다면
valid_to를 NOT NULL 로 두고9999-12-31을 씁니다. 조건이 단순하고 인덱스를 씁니다.