結合先テーブルの論理削除に対応したSQLを書く

TL;DR

LEFT JOIN 結合条件 AND 論理削除の条件を使え。
JOIN句にはANDをつけられるぞ。

はじめに

論理削除とは、次のように削除されたかどうかの情報をカラムに持たせ、SQLを発行する際に常にWHERE句を使って、そのデータを取ってこないようにすることで、そのデータを削除されたかのようにユーザーに見せるテクニック、管理手法のことです。
論理削除の是非は置いておいて、このような設計がなされているテーブルを見ることは多いと思います。

id	company_name	line_name	is_deleted
1	JR北海道	深名線	true
2	JR北海道	名寄本線	true
3	JR北海道	羽幌線	true
4	JR東日本	東北本線	false
5	JR東日本	磐越西線	false
6	JR東日本	岩泉線	true
7	JR東海	身延線	false
8	JR東海	名松線	false
9	JR西日本	三江線	true
10	JR西日本	福塩線	false
11	JR西日本	芸備線	false
12	JR四国	高徳線	false
13	JR四国	予土線	false
14	JR九州	鹿児島本線	false
15	JR九州	三角線	false
16	JR九州	山野線	true

SELECT * FROM railways
WHERE is_deleted = false;

id	company_name	line_name	is_deleted
4	JR東日本	東北本線	false
5	JR東日本	磐越西線	false
7	JR東海	身延線	false
8	JR東海	名松線	false
10	JR西日本	福塩線	false
11	JR西日本	芸備線	false
12	JR四国	高徳線	false
13	JR四国	予土線	false
14	JR九州	鹿児島本線	false
15	JR九州	三角線	false

問題

次のようにテーブルが二つに分かれていて、結合先のテーブルに論理削除を効かせつつ、親テーブルの行の存在の情報だけは得たいとき。

companiesテーブル

id	company_name
1	JR北海道
2	JR東日本
3	JR東海
4	JR西日本
5	JR四国
6	JR九州

railwaysテーブル

id	company_id	line_name	is_deleted
1	1	深名線	true
2	1	名寄本線	true
3	1	羽幌線	true
4	2	東北本線	false
5	2	磐越西線	false
6	2	岩泉線	true
7	3	身延線	false
8	3	名松線	false
9	4	三江線	true
10	4	福塩線	false
11	4	芸備線	false
12	5	高徳線	false
13	5	予土線	false
14	5	鹿児島本線	false
15	6	三角線	false
16	6	山野線	true

こういう情報がほしい

company_name	line_name
JR北海道	NULL
JR東日本	東北本線
JR東日本	磐越西線
JR東海	身延線
JR東海	名松線
JR西日本	福塩線
JR西日本	芸備線
JR四国	高徳線
JR四国	予土線
JR九州	鹿児島本線
JR九州	三角線

誤った解法

SELECT *
FROM companies
LEFT JOIN railways ON company.id = railways
WHERE railways.is_deleted = false;

これだと論理削除された行がまるごと吹き飛んでしまうので、JR北海道の存在がなくなってしまいます。

解決

SELECT *
FROM companies
LEFT JOIN railways ON company.id = railways
                  AND railways.is_deleted = false;

結合先に論理削除がある場合は、JOIN ONの条件を複数にしてください。これで望むようなデータが得られます。

中間テーブルがあるようなリレーションでも

次のように中間テーブルがあるようなリレーションでも同様のことができます

students

id	name
1	一郎
2	二郎
3	三郎

student_subject

id	student_id	subject_id	is_cancelled
1	1	1	0
2	1	2	0
3	1	4	0
4	2	1	0
5	2	2	0
6	2	3	0
7	3	2	1
8	3	3	1

subjects

id	name	is_cancelled
1	微分積分学	0
2	有機化学	0
3	無機化学	0
4	物理化学	1

SELECT * 
FROM students st
LEFT JOIN student_subject ss ON st.id = ss.student_id AND ss.is_cancelled = 0
LEFT JOIN subjects sb ON ss.subject_id = sb.id AND sb.is_cancelled = 0

以下のようなデータが得られるはずです。

name	subject
一郎	微分積分学
一郎	有機化学
二郎	微分積分学
二郎	有機化学
二郎	無機化学
三郎	NULL

ORMのあるようなフレームワークでは

下記のような記事が参考になるかと思います。

EloquentのJOINで結合テーブルに論理削除を効かせる
Qiita https://qiita.com/danishi/items/726fcbed683c03ef389e

【Rails】結合先のテーブルで条件つけたいけど, 結合元のレコードは全部欲しいってときはScoped Association | SharpKnock
https://sharpknock.com/posts/programming/scoped-association.html