これはなに
こんにちは、レバテック開発部のもりたです。
論理削除、皆さんは採用していますか？ わたしが普段開発するシステムでは論理削除を採用しているものもあるのですが、今回はその論理削除の気を付けるべき点として「子テーブルの論理削除されたレコードの絞り込みを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に書き直す作業に戻りますね...。
Discussion
どのDBMSでもその動きになるのでしょうか。
どのDBMSでその動きになったでしょうか。
ありがとうございます！
検証してるのはMySQLですがLEFT JOIN可能なRDBMSならどれでも同じだと思っています！ 昔のOracleはなんかちょっと違うみたいなコメントも拝見しました。
気になるものはぜひご検証ください！
この話は論理削除をnullかそれ以外かで表現しているのであいまいになっていますが、
が子テーブルのレコードを持たない親レコードも除外されるというLEFT JOINの基本機能の話であって、論理削除は本質的な問題ではありません。
なので、「columnの値がNULL」で絞り込みをしたいなら、WHERE以降でやるなということです。
補足ありがとうございます！ その通りで、LEFT JOINの基本機能として、子テーブルのレコードを持たない親レコードも絞り込み対象から除外される（結果に残る）というのが大切な点です。
でせっかくコメントいただいたのでここで言い訳させていただくんですが、
なぜ論理削除をそんな前面に出していたのかというと、論理削除を運用するときのあるあるとしてこのミスがあるからです。
特に、若手バックエンドエンジニアには論理削除をサポートするORMを利用しており、そのORM越しにSQLに触れることが多い人ってのはそれなりの数いるのではないかと思います。こういったORMでは論理削除カラムを指定せずとも、デオフォルトで論理削除を考慮したクエリを発行してくれます。
そういう人がたまに生のクエリをかく時に、ああそうだ論理削除の考慮をしなければと思ってWHERE句に書いちゃう...みたいなのがあるあるなんですよね。これは批判の意図ではなくて、自分もやってます。
というわけで、そういうところで悲しい思いをする人・システムを減らすためにこの記事を書いたという感じになります！ ここまで読まれると思ってなくてタイトルは当初適当で、釣りっぽくなっちゃってたのが申し訳ないです。（たぶん生のクエリ書くのに慣れてる人は違和感強かったと思います）
皆さんのお役に立てれば幸いです〜〜
メモ：記事タイトルを「論理削除の絞り込みはWHERE句でやるな」から「LEFT JOINの論理削除はWHERE句でしぼるな」に変更しました。