内部結合する時の結合条件と制約条件について

質問

内部結合する時の結合条件と制約条件について

下記の例の場合の結合条件と制限条件の書き分けを
教えて下さい。

table1:
no|type
---------
1| A
2| B
3| C
4| A
5| C

table2:
no|type|name|locale
-------------------
1| A |ネコ| ja
2| B |イヌ| ja
3| C |トリ| ja
4| A |cat | en
5| B |dog | en
6| C |bird| en

取得したいデータ：
no|type|name
-------------
1| A |ネコ
2| B |イヌ
3| C |トリ
4| A |ネコ
5| C |トリ

SQL：
SELECT t1.no, t1.type, t2.name
FROM table1 t1
INNER JOIN table2 t2
ON t1.type=t2.type
AND t2.locale='ja'

または

SELECT t1.no, t1.type, t2.name
FROM table1 t1
INNER JOIN table2 t2
ON t1.type=t2.type
WHERE t2.locale='ja'

となると思うのですが、
t2.locale='ja'の条件は
結合条件とするのと制限条件とするのとで
違いがあるのでしょうか？
もし違いがない場合はどちらが一般的でしょうか？

よろしくお願いいたします。

回答No.2

山本 俊男（@yamamoto007）

次の四つ書き方があります。（結果同じ）
SELECT
t1.no ,
t1.type ,
t2.name
FROM table1 t1
INNER JOIN table2 t2
ON t1.type = t2.type
AND t2.locale = 'ja'
/

SELECT
t1.no ,
t1.type ,
t2.name
FROM table1 t1
INNER JOIN table2 t2
ON t1.type = t2.type
where t2.locale = 'ja'
/

SELECT
t1.no ,
t1.type ,
t2.name
FROM table1 t1 ,
table2 t2
where t1.type = t2.type
and t2.locale = 'ja'
/

SELECT
t1.no ,
t1.type ,
t2.name
FROM table1 t1 ,
( select
no ,
name
from table2
where locale = 'ja'
) t2
where t1.type = t2.type

SQLの実行効率面を考えれば、この前の三つは同じです、最後の書き方は一番速いです。

SQL実行ログを参照してください（table1とtable２の十万件のレコードがあるケースを例とする）。

-----　VISUAL SQLTOOL 実行 Log　------------
14:25:48:875 SQL: SELECT ... [ 行 1 - 8 ]
->: 100000 件 0.203秒

14:25:49:140 SQL: SELECT ... [ 行 11 - 18 ]
->: 100000 件 0.203秒

14:25:49:421 SQL: SELECT ... [ 行 21 - 28 ]
->: 100000 件 0.203秒

14:25:49:656 SQL: SELECT ... [ 行 31 - 40 ]
->: 100000 件 0.187秒

参考URL：

http://japan.werunsoft.com/tips.aspx?db=oracle&id=71

お礼

回答ありがとうございます。

実行効率の面からの解説をしていただき、
大変勉強になりました。

質問の例にある書き方はどちらも実行効率は変わらないが
インラインビューを利用した方がパフォーマンスが良いという事でしょうか。

記述方法が複数ある場合、実行効率を調査して選ぶというのも
良い方法ですね。参考になりました。
ありがとうございました。

回答No.1

dda167

結合時に評価されるか(ON)
結合後に評価されるか(WHERE)
の違いがあります。
内部結合の場合、結果セットに違いは生じませんが、
外部結合の場合、結果セットが違ってくるので注意が必要です。

# 外部結合で試してください。面白いですよ。

内部結合の場合、
どちらが一般的かは
私には答えられません。

# 何が普通か
# 何が一般的か
# 現場によって変わります。

お礼

回答ありがとうございます。
お礼が遅れてしまい、申し訳ありません。

内部結合の場合であれば、結果に差異はなく
どちらを選ぶかは現場の状況に合わせて、という事ですね。

>結合時に評価されるか(ON)
>結合後に評価されるか(WHERE)

この表現が大変分かりやすかったです。
ありがとうございました。