目的
このページの目的は、SQL Anywhere でジョイン条件に対して WHERE 句を使用した際の動作を説明します。
概要
SQL Anywhereのクエリでジョイン条件を指定する場合、基本的には、ON句、WHERE句のどちらを使用することも可能です。
ただし、外部ジョインを含むクエリでWHERE句によりNULL入力テーブル（外部ジョインされるテーブル）側のローを制限しようとした場合、クエリオプティマイザーは、その外部ジョインを内部ジョインとして扱う場合があります。
これにより、クエリの実行者が期待したような結果を得られない可能性がありますので注意が必要です。
具体的な現象の例
例として次のようなテーブル TableA と TableB の外部ジョインで説明します。
TableA
| Col1 | Col2 |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
TableB
| Col1 | Col2 |
|---|---|
| 1 | 10 |
| 3 | 30 |
| 5 | 50 |
上記テーブルを作成するSQL文の例
CREATE TABLE TableA(Col1 integer,Col2 char(2), PRIMARY KEY ( "Col1"));
INSERT INTO TableA VALUES(1,'A');
INSERT INTO TableA VALUES(2,'B');
INSERT INTO TableA VALUES(3,'C');
INSERT INTO TableA VALUES(4,'D');
INSERT INTO TableA VALUES(5,'E');
COMMIT;
CREATE TABLE TableB(Col1 integer,Col2 integer, PRIMARY KEY ( "Col1"));
INSERT INTO TableB VALUES(1,10);
INSERT INTO TableB VALUES(3,30);
INSERT INTO TableB VALUES(5,50);
COMMIT;
これらのテーブルで次の図のような左外部ジョインを行うと仮定します。
この場合、もっとも単純なクエリは次のようになります。
SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1;
このときのクエリプランをプランビューアで確認すると次のようになっており、クエリのジョイン条件の記述と同様にTableAに対してTableBが外部ジョイン（JNLO: Left Outer NestedLoopsJoin）されていることがわかります。
また、このときのクエリの結果は次のようになります。
| TableA.Col1 | TableA.Col2 | TableB.Col1 | TableB.Col2 |
|---|---|---|---|
| 1 | A | 1 | 10 |
| 2 | B | NULL | NULL |
| 3 | C | 3 | 30 |
| 4 | D | NULL | NULL |
| 5 | E | 5 | 50 |
しかし、上記のクエリに対してTableBのローを制限するために、次のようにWHERE句で探索条件を加えますと、ジョイン条件の記述とは異なる動作が起こります。
SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1
WHERE TableB.Col2 < 50;
一般的に、このクエリでは次のような結果を期待されると思われます。
| TableA.Col1 | TableA.Col2 | TableB.Col1 | TableB.Col2 |
|---|---|---|---|
| 1 | A | 1 | 10 |
| 2 | B | NULL | NULL |
| 3 | C | 3 | 30 |
| 4 | D | NULL | NULL |
| 5 | E | NULL | NULL |
しかし、実際には次のような結果となります。
| TableA.Col1 | TableA.Col2 | TableB.Col1 | TableB.Col2 |
|---|---|---|---|
| 1 | A | 1 | 10 |
| 3 | C | 3 | 30 |
このときのプランをプランビューアで確認すると次のようになっており、クエリの記述とは異なり、TableBに対するTableAのマージジョイン（JNL: NestedLoopsJoin）が行われています。
これはジョインの際にTableB（NULL入力テーブル）側に一致するローが無いためにセットされたNULL値と、WHERE句の探索条件が比較されたことでUNKNOWNと評価され、対象のローが取り除かれることによるもので、実質的に内部ジョインと同義となると判断されています。
これは期待された動作であり、仕様となります。
期待された結果を得る方法
外部ジョインを含むクエリでローの制限を行うためには、WHERE句ではなく、ON句で条件を記述します。
今回のクエリの場合、次のような記述となります。
SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1
AND TableB.Col2 < 50;
このときのクエリプランをプランビューアで確認すると次のようになっており、クエリのジョイン条件の記述と同様にTableAに対してTableBが外部ジョイン（JNLO: Left Outer NestedLoopsJoin）されています。
また、クエリの結果も期待されているような内容となります。
| TableA.Col1 | TableA.Col2 | TableB.Col1 | TableB.Col2 |
|---|---|---|---|
| 1 | A | 1 | 10 |
| 2 | B | NULL | NULL |
| 3 | C | 3 | 30 |
| 4 | D | NULL | NULL |
| 5 | E | NULL | NULL |
ジョイン条件でのWHERE句の扱いの詳細につきましては下記のドキュメントもご参照ください。
"ジョイン条件内の WHERE 句"
http://dcx.sap.com/index.html#sqla170/ja/html/818c58616ce2101489009e6f8f3df443.html
以上です。
- SAP Managed Tags:
