SQL集計条件の位置とその影響

# 以下のような業務データがあるとします。
顧客表：
・メイン口座とサブ口座を開設可能。（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で結果を抽出してみましたが、結果は予想と違います。

```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句に移動したところ、
予想と同じような結果を抽出できました。

```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' 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が共存の場合）**

# 試行３（最終版）
口座番号に顧客番号が含まれているため、処理としては顧客表は不要になります。

```sql
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する前にテーブルのレコードを減らさないと、パフォーマンスにも
影響がでるでしょう。

追記：
>引用
>>[Criteria on Outer Joined Tables（外部結合されているテーブルに対する基準）](https://weblogs.sqlteam.com/jeffs/2007/05/14/criteria-on-outer-joined-tables/)

上記文書の結論は以下の通りです：
**Never reference OUTER JOIN-ed tables in your WHERE clause.**
**外部結合されているテーブルをWHERE句で参照しないこと！！！**