MySQLのLEFT JOINとNOT EXISTSの違い

データの検証をするためにSQLを書きましたが思った結果になりません。

table_aとtable_bを比較して、table_aだけに存在するデータを得るのが目的です。

■table_a

item_code	color_code	size_code
AAA	BK1	0
AAA	BK2	0
BBB	0	0
CCC	0	S
CCC	0	M

■table_b

id	item_id	color_id	size_id
1	101	2	1
2	101	3	1
3	102	1	1
4	103	1	2
5	103	1	3

■item

id	item_code
101	AAA
102	BBB
103	CCC

■color

id	color_code
1	0
2	BK1
3	BK2

■size

id	size_code
1	0
2	S
3	M

SQL
1select
2  a.item_code,
3  a.color,
4  a.size,
5  b2.id
6from table_a as a
7left join (
8  select
9    b.id
10    i.item_code,
11    c.color_code,
12    s.size_code
13  from table_b as b
14  join item as i on b.item_id = i.id
15  join color as c on b.color_id = c.id
16  join size as s on b.size_id = s.id
17  ) as b2
18on (a.item_code = b2.item_code
19and a.color = b2.color_code
20and a.size = b2.size_code)

table_aの全レコードとtable_bに存在しないデータはb2.idにNULLが入る結果になると思っているのですが、本番データで実行してみると半分くらいb2.idにNULLが入ります。
実際にはほとんどのレコードが一致しているのでこれは期待した結果ではありません。

ちなみにleft joinではなくjoinとすると期待した通りに結合します。

また、やり方を変えて上記のサブクエリとnot existsでセレクトするとtable_aだけに存在するレコードが得られます。

一応目的は果たせているので仕事は進められているのですが、どうしてleft joinだとうまくいかないのかわかりません。

私のleft joinの理解が間違っているのだと思いますが、この結果になる理由を教えていただけないでしょうか。

よろしくお願いします。

not exists句を使ったSQLです、これはうまくいきました

SQL
1select
2  a.item_code,
3  a.color,
4  a.size
5from table_a as a
6where not exists ( select * from 
7 (select
8    b.id
9    i.item_code,
10    c.color_code,
11    s.size_code
12  from table_b as b
13  join item as i on b.item_id = i.id
14  join color as c on b.color_id = c.id
15  join size as s on b.size_id = s.id
16  ) as b2
17where a.item_code = b2.item_code
18and a.color = b2.color_code
19and a.size = b2.size_code
20)

追記

table_aの件数は800件、table_bは810件
table_aとtable_bが一致している数790件
1つ目のSQLの実行結果は800件でNULLが入る件数が400件
1つ目のSQLのleft joinをjoinに変更すると790件になります
2つ目のSQLを実行すると10件になります。

left joinの結果だけがおかしいみたいです。
謎です。。

行動規範の内容に同意します

回答2件

left joinについて
table_aの全レコードとtable_bに存在しないデータはb2.idにNULLが入る結果になる
という認識は誤っていないと思います。
上記のSQLではカンマがなかったり、カラム名が間違っていたりでうまく動きませんでしたが、きちんと試されましたか？

私の環境で実行すると、上記のデータとSQLの結果は下記の通りです。

ちなみに、table_bのid5を削除すると下記の様になります。(id5だけがnullになってます)

再度、ご自身の書いたSQLを見直してみると良いかもしれません。

投稿2017/04/08 02:04

motuo

総合スコア3027

msx2

2017/04/08 02:31

ご回答および検証いただきましてありがとうございます。質問に記載したSQLは実際のSQLを見ながら簡略化して書いたもので間違いがあったとのことで失礼しました。確かに件数が少ないと期待した動きになるみたいです。実データも800件程度とたいした件数ではありませんが、上記一つ目のSQLを実行すると400件くらいNULLが入ります。不思議なのはleft joinをjoinに変更するとleft joinでNULLの入っていないレコードだけが取得できるはずですが、joinだけの方は800件ほど出てきます。 left joinの認識が間違いでなければこの結果はMySQLのバグかも知れませんね。