SQL-92で自己結合での外部結合ができない

Question

SQLServer2005 において、
自己結合＋外部結合が正しく機能しません。
(OS:2003StdR2, SQL Dev. 9.0.1399.06)

例えば、
TableA  ID/Class/Val
というテーブルで、ID/Class がプライマリーキーとします。

ID,Class,Val
10,0001,赤
10,0002,１００本
20,0001,黒

というレコードがある場合、
Where Class='0001'では２レコード出てくる訳で、
自己結合の外部結合で、 Class='0002'とリレーションしたい
んですが、１レコードしか帰ってきません。

=* でクエリー出したら、互換性レベルを下げずに、
Outer Join を使えと言うくせに・・・

NG-Query
 select t1.*, t2.val from TableA t1
  left outer join TableA t2
  on t1.ID = t2.ID
 where t1.Class = '0001'
    and t2.Class = '0002'

互換性を SQL2000(80) に下げてOK-Query
 select t1.*, t2.val from TableA t1, TableA t2
 where t1.Class = '0001'
     and t1.ID *= t2.ID
     and t2.Class = '0002'

以前(4.21の頃？)は、=*のリレーション時には、
条件指定できなかったのが、出来るようになった時は
ありがたかった物ですが・・・

不具合のような気がしますが、他にこういった症状の方は
おられますか？
もしくは私のSQL-92 Outer Join の理解に間違いが
あるとか・・・（＾＾；

Answer 1

外部結合の場合，片方は NULL値になるものが出てきますよね。
LEFT OUTER JOIN なら，
右辺のテーブルからのカラムがNULL値になる行もあるわけです。

WHERE句内で t2.Class = '0002' としたら，
NULL値のものははじかれるので，
左辺のテーブルしかない行は，
外部結合を使っても，結局，はじかれてしまいますよね。
動作としてはあってるような気がするのですが...

結合(qualified join)に使う条件(search condition)は，
結合条件側(join condition つまり ON <search condition>側)での指定となり，
ただ，
外部結合のため，その結合条件が適用されないテーブルへの条件は，
WHERE句側での条件(search condition)での指定になる(と思う)ので，
理屈どうりに書いていくと，

select t1.ID, t1.Class, t1.val as Color, t2.val as Amount 
from TableA t1 left outer join TableA t2
  on t1.ID = t2.ID and t1.Class = '0001' and t2.Class = '0002'
where t1.Class = '0001'

とかでは。

 

 

Answer 2

なるほど。
そういう事なんですね。
確かに、t2.class = '0002' を
Where句からOn句？の後ろに変えれば
期待通りの結果が返って来ました。

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

そういえば、ヘルプを斜め読みして調べている際、
外部結合においては、
WHEREの場所によって、結果が変わる的な記述が
あったような・・・