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

  • 質問

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

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

    ID,Class,Val
    10,0001,赤
    10,0002,100本
    20,0001,黒

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

    =* でクエリー出したら、互換性レベルを下げずに、
    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 の理解に間違いが
    あるとか・・・(^^;

    2006年10月31日 17:17

回答

  • 外部結合の場合,片方は 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'

    とかでは。

     

     

    2006年11月3日 16:24

すべての返信

  • 外部結合の場合,片方は 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'

    とかでは。

     

     

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

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

    そういえば、ヘルプを斜め読みして調べている際、
    外部結合においては、

    WHEREの場所によって、結果が変わる的な記述が
    あったような・・・

    2006年11月4日 5:58