横田です。

JOIN に関して質問があります。現在、次のような2つのテーブルがありまして、

table a: (primary key: mid)
  mid   cid
---------------------------
    1     1
    2     1
    3     1
    4     2
    5     2
    6     3
    ...

table b: (primary key: (mid, uid))
  mid   uid
---------------------------
    1     1
    2     1
    4     1
    5     1
    1     2
    2     2
    3     2
    ...
    
今、table a において cid==1 であるような mid (1,2,3) について、
uid==1 であるようなレコードが table b に存在しないもの (3) を
求めたいと考えました。

そこで、次のようなSQLを考えてみました。

(1) SELECT * FROM a LEFT OUTER JOIN b
ON a.mid=b.mid AND a.cid=1 AND b.uid=1
WHERE b.uid is NULL

ところが、結果は以下のようになってしまいました。

+-----+-----+------+------+
| mid | cid | mid  | uid  |
+-----+-----+------+------+
|   3 |   1 | NULL | NULL |
|   4 |   2 | NULL | NULL |
|   5 |   2 | NULL | NULL |
|   6 |   3 | NULL | NULL |
+-----+-----+------+------+

それでいくつか試してみましたところ、次のようなSQLで目的が達成できま
した。

(2) SELECT * FROM a LEFT OUTER JOIN b
ON a.mid=b.mid AND b.uid=1
WHERE a.cid=1 AND b.uid is NULL

+-----+-----+------+------+
| mid | cid | mid  | uid  |
+-----+-----+------+------+
|   3 |   1 | NULL | NULL |
+-----+-----+------+------+

そこで質問なのですが、
  ・ON 節には複数の条件式をAND演算子でつないだような式（a.mid=b.mid AND
    a.cid=1 AND b.uid=1 のような）を書いてもよいのか？（mySQLマニュアル
    には、ON conditional_expr と書かれているので、よさそうに思えますが…。）
  ・書いてよいなら、なぜSQL(1)は期待通りの動作をしないのか？仮に、結合
    に関係するフィールドだけしかON節には書いてはいけないのならば、なぜ
    SQL(2)は期待通りの動作をするのか？（たまたま？）
  ・SQL(2)が期待通りの動作をするのがたまたまであって、環境依存で動作が
    不定なSQLならば、どういうSQLを書けば環境に依存せずに期待通りの動作
    をさせることができるのか？

なお、当方の環境は mySQL 3.22.32 + VineLinux2.0 です。

よろしくお願いします。

=------------------------------------------------------------=
      株式会社東芝 研究開発センター
        ヒューマンインターフェースラボラトリー
            横田 健彦  takehiko@xxxxxxxxxx
=------------------------------------------------------------=