LEFT JOINの論理削除はWHERE句でしぼるな

これはなに

こんにちは、レバテック開発部のもりたです。
論理削除、皆さんは採用していますか？　わたしが普段開発するシステムでは論理削除を採用しているものもあるのですが、今回はその論理削除の気を付けるべき点として「子テーブルの論理削除されたレコードの絞り込みをWHERE句でしてはならない」という問題について解説します。慣習的に起こりにくいミスなんですが、案外ダメなことを知らない人もいると思うので、ご紹介です。

どうすればいいか？

こうじゃなくて...

SELECT
    *
FROM
    parents
    LEFT JOIN children
        ON parents.id = children.parents_id
WHERE
    parents.deleted_at IS NULL
    AND children.deleted_at IS NULL -- 子テーブルの論理削除の絞り込み
;

こう書いてください。

SELECT
    *
FROM
    parents
    LEFT JOIN children
        ON parents.id = children.parents_id
        AND children.deleted_at IS NULL -- 子テーブルの論理削除の絞り込み
WHERE
    parents.deleted_at IS NULL
;

つまり、子テーブルの論理削除の絞り込みはJOIN句で書いてください^[1]。（慣習的にそうしていた人も多いはず）

なにがまずいのか

論理削除カラムを採用する場合、私たちは「論理削除カラムが有効な場合、そのレコードが物理削除されているかのように振る舞う」ことを期待しているかと思います。早い話がWHERE句に書くとその期待とズレた振る舞いをすることがあります。

もう少し具体的な例を用いて解説します。
以下のようなテーブル構成があるとしましょう。

１対多のシンプルなテーブルふたつで、一つのparentsレコードに複数のchildrenレコードが紐づきます。そして両テーブルともに論理削除のためのカラムを持ちます。
このテーブルに対して、先ほどのWHERE句で論理削除レコードを絞り込んだクエリと、JOIN句で論理削除レコードを絞り込んだクエリを流してみましょう。

WHERE句の場合

こうなります。

ふたつのテーブルを結合し、deleted_atに入力のあるレコードを結果から削除しています。

JOIN ON句の場合

JOIN句の場合はまずdeleted_atに入力のあるレコードを絞り込み、その後結合しています。今回はLEFT JOINのため、childrenテーブルと紐づかないparentsテーブルのレコードはそのまま残りました。

ふたつを見比べた時、JOIN句では残っていたレコードがWHERE句の絞り込みでは結果に出てこないことがわかります。

私たちが「論理削除カラムが有効な場合、そのレコードが物理削除されているかのように振る舞う」ことを期待している以上、LEFT JOINではparentsテーブルのid=3のレコードがそのまま出てくるべきでしょう。

具体的にこんなケースで不具合が

WHERE句での絞り込みのどこが問題なのかについて、具体的な例で考えてみましょう。
例えば、商品テーブルと予約テーブルの関係性だとどうでしょうか。

予約がない商品を抽出したいというニーズがあった時、商品テーブルと予約テーブルがJOINされてSELECTされるでしょう。しかしこのテーブルで予約が１件入ったのちに予約取り消しされた商品があったとします。かつ、具合が悪いことに予約取り消しが論理削除で実装されていた場合、この商品は予約が１件も入っていないのにもかかわらず抽出対象から外れてしまいます。

このように「子テーブルが１件も紐づいてない場合...」というケースで不具合が起きます。１件もない時、何かの処理をさせたいのに、そもそも親が抽出されずに処理が中断されるような事象が発生しそうですね。

不具合の起きる仕組み

ふたつのクエリの比較でも軽く言及していますが、どこに絞り込みの条件を書くかによって、どのタイミングで絞り込まれるかが変わります。これはクエリ内の実行評価順などと呼ばれ、以下の順序で実行されます。

FROM→JOIN→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT/OFFSET

重要なのは「FROM→JOIN→WHERE」の部分で、クエリは結合（JOIN）後にWHEREを実行しています。そのため、結合前後のどちらで論理削除を絞り込むかによって、結合の挙動が変わってしまいます。

論理削除とは「論理削除カラムが有効な場合、そのレコードが物理削除されているかのように振る舞う」ものです。であれば結合前の状態ですでに論理削除されたレコードは絞り込まれているべきです。

parentsテーブルの絞り込みは...

なお、親テーブルの絞り込みはWHERE句で大丈夫です。
LEFT JOINは親テーブルを絞り込まずに結合することを意図しており、絞り込むタイミングがWHEREに限られる^[2]ためになります。

おわりに

論理削除はJOINで絞るって半ば当たり前なんですが、理由までは意識してなかった人もいるのではないでしょうか。
それじゃもりたはWHEREで絞られたコードをJOINに書き直す作業に戻りますね...。

脚注

1. 他にはWITHを使ったり、VIEWを使ったりするなど。今回はとりあえずJOIN句で話を進めます

2. はず