【SQL Server】テーブル結合条件の考察【JOIN】

## 環境
| SQLServer | SQLCMD |
| ---- | ---- |
| 2017 | 14.0.1000.169 |

各種設定はデフォルト。例によってバージョンには依存しないと思われ。

## 前文
テーブル結合の条件は`ON`句に書いても`WHERE`句に書いても同じ。いや、違うっしょ。といった類の話。
自分も理解がぼんやりしていたので検証。メモとして残す。

## 検証テーブル
```SQL:検証用一時テーブル
1> DROP TABLE IF EXISTS #SELF_JOIN;
2> SELECT * INTO #SELF_JOIN
3> FROM (VALUES
4>       ('A', 1)
5>     , ('A', 2)
6>     , ('B', 2)
7>     , ('B', 3)
8> )t(GRP, VAL)
9> ;
10>
11> SELECT * FROM #SELF_JOIN ORDER BY GRP, VAL;
12> GO

(4 行処理されました)
GRP VAL
--- -----------
A             1
A             2
B             2
B             3

(4 行処理されました)
```

一時テーブル`#SELF_JOIN`内の、`GRP`列が`'A'`のレコードと`'B'`のレコードの結合、出力を着地点とする。

## 内部結合（INNER JOIN）
まず`ON`句に結合条件を記述するパターン。
```SQL:ON句に結合条件
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> INNER JOIN #SELF_JOIN       B
8>     ON  A.VAL = B.VAL
9>     AND A.GRP = 'A'
10>     AND B.GRP = 'B'
11> ;
12> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               2 B               2

(1 行処理されました)
```

次は`WHERE`句に結合条件を記述するパターン。
```SQL:WHERE句に結合条件
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> INNER JOIN #SELF_JOIN       B
8>     ON  A.VAL = B.VAL
9> WHERE   A.GRP = 'A'
10>     AND B.GRP = 'B'
11> ;
12> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               2 B               2

(1 行処理されました)
```

両実行結果は同じであり、結果も想定通り。問題なし。

## 外部結合（LEFT OUTER JOIN）
まず`ON`句に結合条件を記述するパターン。

```SQL:ON句に結合条件
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> LEFT OUTER JOIN #SELF_JOIN  B
8>     ON  A.VAL = B.VAL
9>     AND A.GRP = 'A'
10>     AND B.GRP = 'B'
11> ORDER BY
12>       A.GRP
13>     , A.VAL
14>     , B.GRP
15>     , B.VAL
16> ;
17> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2
B               2 NULL         NULL
B               3 NULL         NULL

(4 行処理されました)
```

結合条件で絞り込まれる、と思い込んでいると、この結果は理解できない。`A.GRP = 'A'`としているのに、何故`A.GRP`列が`'B'`のレコードが出力されるの？ と。
しかし結合仕様としてはこれが正しい。
`ON`句で指定するのは`結合条件`。`A.GRP = 'A'` `B.GRP = 'B'`とは、文字通り`A.GRP`列が`'A'`のレコードと`B.GRP`列が`'B'`のレコードを結合する、という意味。
そして`外部結合`とは`FROM`句で指定したテーブルのレコードを全て列挙する、という仕様。
従って、`A.GRP`列が`'B'`のレコードも出力される。そして結合条件は満たしていないので、接続先は`NULL`として出力されると。

しかしこれは期待した結果ではない。`FROM`句側で指定したレコードは、`A.GRP = 'A'`で絞り込みたいので`WHERE`句で指定する必要がある。

```SQL:WHERE句を指定
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> LEFT OUTER JOIN #SELF_JOIN  B
8>     ON  A.VAL = B.VAL
9>     AND B.GRP = 'B'
10> WHERE   A.GRP = 'A'
11> ORDER BY
12>       A.GRP
13>     , A.VAL
14>     , B.GRP
15>     , B.VAL
16> ;
17> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2

(2 行処理されました)
```

これが期待した結果。
前述内部結合（INNER JOIN）は積結合を返す。平たく言えば、結合条件に該当するレコードだけを返す。従って、結合条件が絞り込み条件でもある。
しかし外部結合は結合条件イコール絞り込み条件ではない。厳密には、結合先は絞り込まれるけど、結合元は絞り込まれない。ここら辺が混同されがちでは。

## 完全外部結合（FULL OUTER JOIN）
ついでに。


```SQL:ON句のみ指定
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> FULL OUTER JOIN #SELF_JOIN  B
8>     ON  A.VAL = B.VAL
9>     AND A.GRP = 'A'
10>     AND B.GRP = 'B'
11> ORDER BY
12>       ISNULL(A.GRP, 'Z')
13>     , A.VAL
14>     , B.GRP
15>     , B.VAL
16> ;
17> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2
B               2 NULL         NULL
B               3 NULL         NULL
NULL         NULL A               1
NULL         NULL A               2
NULL         NULL B               3

(7 行処理されました)
```

当然期待した結果ではない。強いて`WHERE`句を指定するなら以下か。


```SQL:WHERE句も指定
1> SELECT
2>       A.GRP     [A.GRP]
3>     , A.VAL     [A.VAL]
4>     , B.GRP     [B.GRP]
5>     , B.VAL     [B.VAL]
6> FROM #SELF_JOIN             A
7> FULL OUTER JOIN #SELF_JOIN  B
8>     ON  A.VAL = B.VAL
9>     AND A.GRP = 'A'
10>     AND B.GRP = 'B'
11> WHERE   A.GRP = 'A'
12>     OR  B.GRP = 'B'
13> ORDER BY
14>       ISNULL(A.GRP, 'Z')
15>     , A.VAL
16>     , B.GRP
17>     , B.VAL
18> ;
19> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2
NULL         NULL B               3

(3 行処理されました)
```

間違ってはいないと思うけど、イマイチ釈然としない。この気持ち悪さは、`ON`句と`WHERE`句の指定が被っていて正規化されていないからかな。かといって`ON`句が`A.VAL = B.VAL`だけでは正しく機能しない。
少々冗長になってしまうけど、サブクエリなり`WITH`句でレコードを明示的に分けてからの結合が分かり易いかも。


```SQL:WITH句版完全外部結合
1> ;WITH A AS (
2>     SELECT * FROM #SELF_JOIN WHERE GRP = 'A'
3> )
4> , B AS (
5>     SELECT * FROM #SELF_JOIN WHERE GRP = 'B'
6> )
7> SELECT
8>       A.GRP     [A.GRP]
9>     , A.VAL     [A.VAL]
10>     , B.GRP     [B.GRP]
11>     , B.VAL     [B.VAL]
12> FROM            A
13> FULL OUTER JOIN B
14>     ON  A.VAL = B.VAL
15> ORDER BY
16>       ISNULL(A.GRP, 'Z')
17>     , A.VAL
18>     , B.GRP
19>     , B.VAL
20> ;
21> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2
NULL         NULL B               3

(3 行処理されました)
```

外部結合も同様に書けば以下。

```SQL:WITH句版外部結合
1> ;WITH A AS (
2>     SELECT * FROM #SELF_JOIN WHERE GRP = 'A'
3> )
4> , B AS (
5>     SELECT * FROM #SELF_JOIN WHERE GRP = 'B'
6> )
7> SELECT
8>       A.GRP     [A.GRP]
9>     , A.VAL     [A.VAL]
10>     , B.GRP     [B.GRP]
11>     , B.VAL     [B.VAL]
12> FROM            A
13> LEFT OUTER JOIN B
14>     ON  A.VAL = B.VAL
15> ORDER BY
16>       A.GRP
17>     , A.VAL
18>     , B.GRP
19>     , B.VAL
20> ;
21> GO
A.GRP A.VAL       B.GRP B.VAL
----- ----------- ----- -----------
A               1 NULL         NULL
A               2 B               2

(2 行処理されました)
```
## 補足
テーブルの自己結合で例を挙げたけど、当然別テーブルでの結合も同じ理屈。
分かっている人には今更な内容だろうけど。