環境

SQLServer	SQLCMD
2017	14.0.1000.169

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

前文

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

検証テーブル

検証用一時テーブル

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句に結合条件を記述するパターン。

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句に結合条件を記述するパターン。

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句に結合条件を記述するパターン。

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句で指定する必要がある。

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）

ついでに。

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句を指定するなら以下か。

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句でレコードを明示的に分けてからの結合が分かり易いかも。

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 行処理されました)

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

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 行処理されました)

補足

テーブルの自己結合で例を挙げたけど、当然別テーブルでの結合も同じ理屈。
分かっている人には今更な内容だろうけど。