固定値を含む結合と複数テーブルの結合について

質問

「１．固定値を含む結合」と「２．複数テーブルの結合」について教えて下さい。

最近oracleを使い始めてSQLで表の結合を書く場面があるんですが
LEFT（RIGHT) JOIN を使うのか、(+)を使って書くのかで迷っています。

LEFT(RIGHT) JOINだと固定値を含めて外部結合しようとするとエラーになってしまいます。
それが嫌で(+)を使っていたんですが
今度は複数のテーブルを(+)で結合しようとするとエラーになってしまいます。
（ＡテーブルとＢテーブル、ＡテーブルとＣテーブルなど？）

こういう場合はどうすれば良いのでしょうか？
とりあえず副参照をFROMに指定して対処してるんですが
何かきちんとした書き方があるのでしょうか？誰か教えて下さい。

回答No.1

anmochi · ベストアンサー

話が抽象的すぎて何を答えれば良いのか分からない・・・・　「エラーになってしまいます。」が指す意味が分かりづらいですね。ORA-xxxxxが発生するのか、意図したレコードが抽出されないのか。もう少し具体的に書くと良いでしょう。また、その際に2テーブルの列定義（抜粋でも）やSQLのON句などは書かないと回答はつかないでしょうね。

　Oracleの10g以降を使用しているのであればもはや(+)を使う理由はありません。LEFT JOINを使うべきです。

　ところで、外部結合において良くある間違いとして以下のようなSQLがあります。ID列とNAME列があるテーブルA、Bがあり、

SELECT A.ID, B.ID, B.NAME FROM A LEFT JOIN B ON
A.ID = B.ID
AND
B.NAME = 'テスト'

というSQLを発行するとします。これは外部結合が全く意味をなさないSQLになります。これは

SELECT A.ID, B.ID, B.NAME FROM A LEFT JOIN B ON
A.ID = B.ID
AND
'テスト' = B.NAME

というSQLでないといけません。つまり、LEFT JOINのON句に指定される結合条件は、外部結合されるBが全て右側に来なくてはいけません。初歩的ですがありがちなミスなので覚えておくと良いでしょう。また、場合によってはB側をサブクエリーにするというテクニックが適用できる事もあります。

SELECT A.ID, B.ID, B.NAME FROM A
LEFT JOIN (SELECT * FROM B WHERE NAME = 'テスト') B
ON A.ID = B.ID

お礼

回答ありがとうございます。今は
＞サブクエリーにするというテクニック
これで対処してるって状態です。

＞A.ID = B.ID
＞AND
＞'テスト' = B.NAME

正直どっちに固定値を書いたか記憶がないんですよね。
順番が逆だとエラーになるんでしょうか？
それとも単純に書き間違えたのかな…。
とりあえず、会社じゃないと確認出来ないんで来週確認してみます。

補足

単に間違ってただけでしたorz
言われた通りにやったら上手くいきました
ありがとうございます

回答No.2

yamada_g

回答についてはNo.1さんのおっしゃるとおりなのですが、
>ところで、外部結合において良くある間違いとして以下のようなSQLがあります。・・
というのはどういうことでしょうか？

たとえば

with a as (
select 1 id from dual
union all select 2 id from dual
)
,b as (
select 1 id ,'テスト' name from dual
union all select 2 id ,'' name from dual)
select
a.id aid,b.id bid,b.name
from a
left join b
on a.id = b.id
and b.name = 'テスト';

とした場合、

AID BID NAME
1 1 "テスト"
2 "" ""

となりますが、これは
>外部結合が全く意味をなさないSQL
なのでしょうか？
自分としては正しい外部結合になっていると思うのですが。
oracle10.2.0.1.0で確認しました。
便乗質問で申し訳ありませんが教えていただけたら幸いです。