目的

このページの目的は、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;

 

 

これらのテーブルで次の図のような左外部ジョインを行うと仮定します。

LeftOuterJoin1.png

 

この場合、もっとも単純なクエリは次のようになります。

SELECT * FROM TableA LEFT OUTER JOIN TableB

ON TableA.Col1 = TableB.Col1;

 

 

このときのクエリプランをプランビューアで確認すると次のようになっており、クエリのジョイン条件の記述と同様にTableAに対してTableBが外部ジョイン(JNLO: Left Outer NestedLoopsJoin)されていることがわかります。

OuterJoin2.png

また、このときのクエリの結果は次のようになります。

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)が行われています。

InnerJoin2.png

これはジョインの際に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)されています。

OuterJoin3.png

 

また、クエリの結果も期待されているような内容となります。

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

 

以上です。

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply