複数テーブルから、現行データをビューで取得したときの躓き

はじめに

Oracleで複数のテーブルから現行データだけをまとめたビューを作成しようとしたときに、
単純に結合して「古いデータが混ざる」「重複が増える」といった問題が発生したので、そのときのポイントを備忘を兼ねて整理しました。

LEFT JOINとWHEREの条件の使い分け
TRUNCによる日付の比較

NG：WHERE に結合先の条件を書いた場合

SELECT *
FROM main_table main
LEFT JOIN secondary_records sec
    ON main.id = sec.main_id
WHERE TRUNC(sec.start_date) <= TRUNC(SYSDATE)
  AND (sec.end_date IS NULL OR TRUNC(sec.end_date) >= TRUNC(SYSDATE));

問題点

LEFT JOINなのにWHEREに条件を書いているため、INNER JOINのような挙動になる
結合先に対応する行がNULLの場合、mainの行も除外される
「結合先が存在しない現行データ」がビューに含まれなくなる

NG：TRUNC を使わず日付を比較する

SELECT *
FROM main_table
WHERE start_date <= SYSDATE
  AND (end_date IS NULL OR end_date >= SYSDATE);

問題点

OracleのDATE型は時刻まで持っているので、意図しない行が抽出されることがある

正しく抽出するために

LEFT JOIN で現行データを絞る

結合時点で不要な行を除外できる
WHEREの条件がシンプルになる

LEFT JOIN secondary_records sec
  ON main.id = sec.main_id
  AND TRUNC(sec.start_date) <= TRUNC(SYSDATE)
  AND (sec.end_date IS NULL OR TRUNC(sec.end_date) >= TRUNC(SYSDATE))

TRUNCを使った日付比較

TRUNCによって時刻情報を切り捨て、日付単位で比較することで漏れや誤抽出を防止

SELECT *
FROM main_table
WHERE TRUNC(start_date) <= TRUNC(SYSDATE)
  AND (end_date IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE));

ビューの例

CREATE OR REPLACE VIEW v_current_records AS
SELECT DISTINCT 
    main.id,
    main.name,
    sec.info AS secondary_info,
    tert.info AS tertiary_info,
    main.start_date,
    COALESCE(main.end_date, sec.end_date, tert.end_date) AS end_date
FROM main_table main
LEFT JOIN secondary_records sec
    ON main.id = sec.main_id
    AND TRUNC(sec.start_date) <= TRUNC(SYSDATE)
    AND (sec.end_date IS NULL OR TRUNC(sec.end_date) >= TRUNC(SYSDATE))
LEFT JOIN tertiary_records tert
    ON main.id = tert.main_id
    AND TRUNC(tert.start_date) <= TRUNC(SYSDATE)
    AND (tert.end_date IS NULL OR TRUNC(tert.end_date) >= TRUNC(SYSDATE))
WHERE
    TRUNC(main.start_date) <= TRUNC(SYSDATE)
    AND (main.end_date IS NULL OR TRUNC(main.end_date) >= TRUNC(SYSDATE));

まとめ

LEFT JOINの条件に抽出したい条件を設定したほうがよい
dateでの比較はTRUNCで行う