環境
| 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句に結合条件を記述するパターン。
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句に結合条件を記述するパターン。
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句に結合条件を記述するパターン。
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句で指定する必要がある。
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）
ついでに。
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句を指定するなら以下か。
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句でレコードを明示的に分けてからの結合が分かり易いかも。
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 行処理されました)
外部結合も同様に書けば以下。
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 行処理されました)
補足
テーブルの自己結合で例を挙げたけど、当然別テーブルでの結合も同じ理屈。
分かっている人には今更な内容だろうけど。