LEFT JOINの条件式で＝’’を使用した場合

質問

以下のSQLを実行した場合、レコードを1件取得して欲しいのですが、
なぜか上手く取得できません。

ご存知の方がいらっしゃいましたら、教えてください。

-- SQL1 ---------------------------------------------------

select
　　MAIN.MAIN_NAME,
　　nvl(SUB1.CODE is NULL, 0) as SUB_CODE1,
　　nvl(SUB2.CODE is NULL, 0) as SUB_CODE2
from
　　MAIN_TABLE MAIN
　　　left join SUB_TABLE SUB1 on MAIN.CODE = SUB1.CODE
　　　　　　　　　　　　　　　　　　　and SUB1.ZYOUKEN = '1'　　　　(1)
　　　left join SUB_TABLE SUB2 on MAIN.CODE = SUB2.CODE
　　　　　　　　　　　　　　　　　　　and SUB2.ZYOUKEN = ''　　　　　(2)
where
　　MAIN.CODE = 'XXX'

-----------------------------------------------------------

質問１．
どうも、(2)の部分を空文字(?)ではなく何か値を入れると正常に1件
取得できるようなのですが、なぜ外部結合しているにもかかわらず
0件になってしまうのかが良く分かりません。

質問２．
やはり条件に空文字を使用するのはよろしくないでしょうか？

※補足
念のため、以下のSQLを実行したところ、こちらは問題なく1件取得
できているので、抽出条件は間違っていないです。

-- SQL2 ---------------------------------------------------

select
　　MAIN.MAIN_NAME
from
　　MAIN_TABLE MAIN
where
　　MAIN.CODE = 'XXX'

-----------------------------------------------------------

以上です。宜しくお願いします。

回答No.4

tsuduki123 · ベストアンサー

autotrace あたりで自分の意図したとおりのものになっているか確認してみてはどうでしょう?

WHERE句が指定されているので、実行計画が等価結合に書き換わっちゃっている可能性があります。

昔、これではまったことがあるので
僕は外部結合するときはWHEREは指定しないようにして
ON句で条件を指定するようにしています。
# 個人的にはバグじゃないかと思うんだけど仕様らしい。

たとえば、
WHERE句の条件を(1)あたりに追加してみてうまくいくようならこのケースかなと。

お礼

なるほど！

私もバグのような気がしますが、仕様・・・ですか。

>>WHERE句の条件を(1)あたりに追加してみてうまくいくようならこのケースかなと。
社内に再現できる環境が無いので、次の機会に試してみます。

有難うございました。

回答No.5

ishi7474

今のところ(11.2まで)Oracle Databaseは長さ0の文字列をNULLとして処理するので、

... and SUB2.ZYOUKEN = ''

ではなくて

... and SUB2.ZYOUKEN IS NULL

で条件にマッチしませんか(SUB2.ZYOUKENがVARCHAR2型という前提ですが)。

お礼

とりあえず、変数が空白の場合は、ダミーの文字を入れて
「= ''」とならないようにすることにしました。

有難うございました。

回答No.3

Chronos198

とりあえず質問２についてですが、
NULL=NULLやNULL=''や''=''は真ではないですよ。

OracleにおいてNULLというのは「なんだかわからないもの」なので、
「なんだかわからないもの１」は「なんだかわからないもの２」と等しいかと聞いても、
「わからない」といわれてしまいます。

Select 'HOGE' From Dual Where NULL=NULL;
Select 'HOGE' From Dual Where NULL='';
Select 'HOGE' From Dual Where ''=NULL;
Select 'HOGE' From Dual Where ''='';
Select 'HOGE' From Dual Where NULL Is NULL;
Select 'HOGE' From Dual Where '' Is NULL;

この辺を実行して結果を確認してみてください。
Is NULL以外だと、NVL( HOGE, 'NULL' )='NULL'のような方法もあります。

個人的にはNo.1の方と同じくNULLは好きではないので、最初から固定長空白で埋める派です。

質問１について、
「1行取得したいのだが、上手く取得出来ない」というのは具体的にどのような状態でしょう？
Oracleエラーが出るだとか、レコードが選択されないだとか、複数選択されてしまうとか。

nvl(SUB1.CODE is NULL, 0) as SUB_CODE1,

という記述を初めて見るのですが、これはエラーが発生しませんか？

NVL( HOGE, 0 )

と記述すると、HOGEがNULLの時に0が返される、という関数だったと思うのですが。

お礼

とりあえず、変数が空白の場合は、ダミーの文字を入れて回避することにしました。

有難うございました。

補足

ご丁寧に有難うございます。

>NULL=NULLやNULL=''や''=''は真ではないですよ。

もちろんこれは承知しています。
私の書き方が悪かったようですみません。
No.1の方に補足させて頂きましたので、宜しければそちらご覧下さい。

>「1行取得したいのだが、上手く取得出来ない」というのは具体的にどのような状態でしょう？

レコードが1件も選択されない状態です。

>nvl(SUB1.CODE is NULL, 0) as SUB_CODE1,
という記述を初めて見るのですが、これはエラーが発生しませんか？

確かにこの記述はおかしいですね。
↓が正しいです。
　nvl(SUB1.CODE , 0) as SUB_CODE1,
　nvl(SUB1.CODE , 0) as SUB_CODE1,

回答No.2

nda23

結合条件が定数というのは.・・・
こういう場合はサブクエリを使う方が効率が
よいことが多いものです。

LEFT JOIN (SELECT * FROM SUB_TABLE
WHERE ZYOUKEN = '') SUB2
ON MAIN.CODE = SUB2.CODE

こういう感じですね。つまり、最初から量を
絞ったクエリと結合させる方が、結合させ
てから絞るより速いということです。

お礼

なるほど、勉強になります！

ありがとうございました。

回答No.1

STICKY2006

>>SUB2.ZYOUKEN = ''

SUB_TABLEのZYOUKENの中身が「スペース」「NULL」ってことは？

空白条件にする場合は
RTRIM(LTRIM((SUB2.ZYOUKEN)) = ''とかよくやってますが。

オラクルじゃなくてSQLServerなので文法はお察しください。

ちなみに、NULLの場合は。。。大嫌いな処理なのでおいらが組む場合は極力NULLを使わないシステムにしてるので参考文は書きません。はい。

select
*
from
SUB_TABLE
where
ZYOUKEN = ''

これと同等ですよね。結合しなきゃですが。

お礼

とりあえず、変数が空白の場合は、ダミーの文字を入れて回避することにしました。

有難うございました。

補足

回答有難うございます。

>>SUB_TABLEのZYOUKENの中身が「スペース」「NULL」ってことは？
これは設計上ありえません。
なので、別にZYOUKENの中身が''のデータを取得したい訳では無いのです。

少々書き方が悪かったようですみません。
上記SQLでは(1),(2)の右辺を固定で'1'、''と記載しましたが、
正確には変数１、変数２です。

場合によっては変数の中身が無いことがあるので、その場合右辺が''となります。

また、上記SQLを実行する意図ですが、
　１．MAIN_TABLEにCODE='XXX'のレコードが存在するか。
　２．SUB_TABLEにCODE='XXX'　かつ　ZYOUKEN=変数１
　　　のレコードが存在するか。
　３．SUB_TABLEにCODE='XXX'　かつ　ZYOUKEN=変数２
　　　のレコードが存在するか。

この３つのチェックを行いたいのですが、MAIN_TABLEにCODE="XXX"
のレコードが存在しているのに、結合条件が''になったときに
なぜデータが1件も取得できないのかが疑問に思ったところです。