以下のような業務データがあるとします。
顧客表：
・メイン口座とサブ口座を開設可能。（Mはメイン、Sはサブ）
・メイン口座のみを持つ顧客も存在。
・サブ持っていない場合、空きの文字列で保存
| 顧客番号 | メイン口座番号 | サブ口座番号 |
|---|---|---|
| 0001 | 0001M | 0001S |
| 0002 | 0002M | 0002S |
| 0003 | 0003M | '' |
残高表：
・口座番号で管理
・三種類の区分
| 口座番号 | 金額 | 区分 |
|---|---|---|
| 0001M | 1000 | 株 |
| 0001M | 1000 | 現金 |
| 0001M | 1000 | 不動産 |
| 0001S | 100 | 株 |
| 0001S | 100 | 現金 |
| 0001S | 100 | 不動産 |
| 0002M | 2000 | 株 |
| 0002M | 2000 | 現金 |
| 0002M | 2000 | 不動産 |
| 0002S | 200 | 株 |
| 0002S | 200 | 現金 |
| 0002S | 200 | 不動産 |
| 0003M | 3000 | 株 |
| 0003M | 3000 | 現金 |
| 0003M | 3000 | 不動産 |
| （0003の顧客はサブなし、即ち0003Sがないことをご注意ください。） |
目的：顧客ごとでメイン口座とサブ口座の残高合計を抽出
＊：区分は現金のみ
| メイン口座 | サブ口座 | 合計 |
|---|---|---|
| 0001M | 0001S | 1100 |
| 0002M | 0002S | 2200 |
| 0003M | 3000 |
業務視点からの設計
残高表にメイン口座とサブ口座レコードは混在しているため、
１、まずは顧客表から顧客IDを抽出
２、残高表からメイン口座のレコードを抽出して上記１の結果と結合
３、もう一回残高表からサブ口座のレコードを抽出して上記２までの結果と結合
また、サブ口座が存在しない状況もあるので、顧客表にLEFT JOINすると考えていました。
試行１
上記内容を踏まえて、以下のSQLで結果を抽出してみましたが、結果は予想と違います。
select
t2.kouza as main_kouza,
t3.kouza as sub_kouza,
case when t3.amount is not null then t2.amount + t3.amount
else t2.amount
end as sum
from
(
select
id
from
kokyaku
)as t1
left join zandaka as t2
on left(t2.kouza, 4) = t1.id and
substring(t2.kouza from '.$') = 'M'
left join zandaka as t3
on left(t3.kouza, 4) = t1.id and
substring(t3.kouza from '.$') = 'S'
where
t2.kbn = '現金' and
t3.kbn = '現金'
結果
| メイン口座 | サブ口座 | 合計 |
|---|---|---|
| 0001M | 0001S | 1100 |
| 0002M | 0002S | 2200 |
| サブ口座を持っていない顧客情報は抽出されませんでした。 |
試行２
区分条件をwhereではなく、各テーブルのJOINのON句に移動したところ、
予想と同じような結果を抽出できました。
select
t2.kouza as main_kouza,
t3.kouza as sub_kouza,
case when t3.amount is not null then t2.amount + t3.amount
else t2.amount
end as sum
from
(
select
id
from
kokyaku
)as t1
left join zandaka as t2
on left(t2.kouza, 4) = t1.id and
substring(t2.kouza from '.$') = 'M' and
t2.kbn = '現金'
left join zandaka as t3
on left(t3.kouza, 4) = t1.id and
substring(t3.kouza from '.$') = 'S' and
t3.kbn = '現金'
結果
| メイン口座 | サブ口座 | 合計 |
|---|---|---|
| 0001M | 0001S | 1100 |
| 0002M | 0002S | 2200 |
| 0003M | 3000 |
試行１と試行２結果が異なる原因
結合の処理順番は原因になると見られます。
試行１では、区分条件は最後のwhereにあるので、
各テーブルを結合してから、区分によってレコードが結果表削除されます。
しかし、結合の処理で、t3、即ちサブ口座表に、0003に該当するデータがないため
結合した結果表に、0003の行で、t3.kbnと相当するレコードはNULLになります。
| 顧客番号 | 口座（メイン） | ... | 区分 | 口座（サブ） | ... | 区分 | ... |
|---|---|---|---|---|---|---|---|
| 0001 | 0001M | ... | 現金 | 0001S | ... | 現金 | ... |
| 0001 | 0001M | ... | 現金 | 0001S | ... | 株 | ... |
| 0001 | 0001M | ... | 現金 | 0001S | ... | 不動産 | ... |
| 0002 | 0002M | ... | 現金 | 0002S | ... | 現金 | ... |
| 0002 | 0002M | ... | 現金 | 0002S | ... | 株 | ... |
| 0002 | 0002M | ... | 現金 | 0002S | ... | 不動産 | ... |
| 0003 | 0003M | ... | 現金 | NULL | ... | NULL | ... |
where句にある条件は、結果テーブルの全体に影響するため、
ここに「t3.kbn = '現金'」入れると、最後の一行レコードが削除され、
0003のデータがなくなります。
JOINのON句にある条件は、結合する前のテーブル（サブクエリのほうで）を
影響するため、ここに区分条件を入れると、レコード2行のテーブル（サブ）が
レコード3行のテーブル（メイン）にLEFT JOINされるため、0003のデータは残ります。
※LEFT JOINの定義により、少なくとも結合先のテーブルのレコード数は確保できます。
このため、最後の結果に対して、少なくとも顧客数、もしくはメイン口座数を確保したい場合
顧客表またはメイン口座のサブクエリ結果にLEFT JOINしたほうがよい、WHEREの使用は慎重に
検討しないといけないと思います。（特にJOINとWHEREが共存の場合）
試行３（最終版）
口座番号に顧客番号が含まれているため、処理としては顧客表は不要になります。
select
t1.kouza,
t2.kouza,
--nullのカラムがあれば合計はでないので、caseで処理
case when t2.amount is not null then t1.amount + t2.amount
else t1.amount
end
from zandaka as t1
--同じ顧客のサブ口座のみを抽出、さらに区分を現金に限定
left join zandaka as t2
--サブ口座該当判定
on substring(t2.kouza from '.$') = 'S' and
--同じ顧客であるかを判定
left(t1.kouza, 4) = left(t2.kouza, 4) and
--現金限定
t2.kbn = '現金'
where
t1.kbn = '現金' and
substring(t1.kouza from '.$') = 'M'
まとめ
・WHERE句にある条件はテーブルが結合されてから結果テーブルに適用される
＊：結合した結果にNULLのレコードがある場合、結合されたテーブルのカラムを
条件としてWHERE句に入れると、結果は不完全になるかもしれない
・JOINのON句にある条件が適用されてからテーブルは結合される
また、JOINする前にテーブルのレコードを減らさないと、パフォーマンスにも
影響がでるでしょう。
追記：
引用
上記文書の結論は以下の通りです：
Never reference OUTER JOIN-ed tables in your WHERE clause.
外部結合されているテーブルをWHERE句で参照しないこと！！！