はじめに
業務系のシステム開発をしていると、トランにマスタをぶつける際に外部結合にすることがあります。
これは、万が一、マスタが取得できなくても検索対象となるべきトランの欠落を防ぐためです。
但し、外部結合は、絞込み条件をどこに書くかによって検索結果は異なります。安全のために外部結合にした結果、バグを埋め込んでしまっては元も子もないので気を付けましょう。
具体例
対象とするテーブル
【受注T】
| 受注NO | 商品CD | 金額 |
|---|---|---|
| 001 | AAA | 100 |
| 002 | BBB | 300 |
| 003 | CCC | 500 |
【商品M】（商品CD='BBB'が欠落）
| 商品CD | 商品名 | 商品区分 |
|---|---|---|
| AAA | 商品A | 0 |
| CCC | 商品B | 1 |
マスタが欠落しても外部結合していれば、受注データは表示される
select
T.受注NO
, T.商品CD
, M.商品名
, T.金額
from
受注T T
left outer join 商品M M
on T.商品CD = M.商品CD
| 受注NO | 商品CD | 商品名 | 金額 |
|---|---|---|---|
| 001 | AAA | 商品A | 100 |
| 002 | BBB | null | 300 |
| 003 | CCC | 商品C | 500 |
- 商品名は表示されないレコードが存在するが、受注Tの対象データは全て検索されている。
- select するのは、T.商品CD で、M.商品CD でないことに注意。
商品区分='1'の商品の受注のみを表示する（誤り）
誤り
select
T.受注NO
, M.商品CD
, M.商品名
, T.金額
from
受注T T
left outer join 商品M M
on T.商品CD = M.商品CD
and M.商品区分 = '1'
| 受注NO | 商品CD | 商品名 | 金額 |
|---|---|---|---|
| 001 | AAA | null | 100 |
| 002 | BBB | null | 300 |
| 003 | CCC | 商品C | 500 |
- 絞り込んだ後で、外部結合しているので、受注Tの全レコードが検索される。
商品区分='1'の商品の受注のみを表示する（考察→成果へ）
結果オーライ
select
T.受注NO
, M.商品CD
, M.商品名
, T.金額
from
受注T T
left outer join 商品M M
on T.商品CD = M.商品CD
where
M.商品区分 = '1'
| 受注NO | 商品CD | 商品名 | 金額 |
|---|---|---|---|
| 003 | CCC | 商品C | 500 |
- 外部結合した後に絞り込む。
- 但し、上記の場合は、商品Mに存在しない受注Tのレコードの商品区分は null になるため、left outer join ではなく、inner join で結合するのが正しい。
上記を踏まえた修正
select
T.受注NO
, M.商品CD
, M.商品名
, T.金額
from
受注T T
inner join 商品M M
on T.商品CD = M.商品CD
where
M.商品区分 = '1'
- inner join にした場合は、inner join の条件部分で絞り込んでも、where 条件で絞り込んでも、結果は同じになるが、個人的には、inner join の中で先に絞り込んだ商品Mと受注Tを結合した方がよいと思う。（最近のDBは賢いので、どっちで書いても実行計画は同じになることが多い。）
最終版
select
T.受注NO
, M.商品CD
, M.商品名
, T.金額
from
受注T T
inner join 商品M M
on T.商品CD = M.商品CD
and M.商品区分 = '1'
番外編
結果はあってるけど、おかしなSQL
結果があえばよいってわけじゃないぞ！
select
T.受注NO
, M.商品CD
, M.商品名
, T.金額
from
受注T T
left outer join 商品M M
on T.商品CD = M.商品CD
and M.商品区分 = '1'
where
M.商品CD is not null
- 受注Tの全件に対して商品Mをぶつけた後に絞り込むので効率が悪いし、そもそも、SQL の組み立て方からしておかしいてす。
おわりに
そもそもテーブル間の整合性が担保できないDBに問題があるのですが、世の中のシステムは結構いい加減です。いい加減なシステムのSQLは、いい加減なものがちょこちょこあって、改めて基本を押さえましょうという意味で書きました。