質問編集履歴

2 結果件数を追加

msx2

msx2 score 164

2017/04/08 11:43  投稿

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
select
 a.item_code,
 a.color,
 a.size,
 b2.id
from table_a as a
left join (
 select
   b.id
   i.item_code,
   c.color_code,
   s.size_code
 from table_b as b
 join item as i on b.item_id = i.id
 join color as c on b.color_id = c.id
 join size as s on b.size_id = s.id
 ) as b2
on (a.item_code = b2.item_code
and a.color = b2.color_code
and 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
select
 a.item_code,
 a.color,
 a.size
from table_a as a
where not exists ( select * from
(select
   b.id
   i.item_code,
   c.color_code,
   s.size_code
 from table_b as b
 join item as i on b.item_id = i.id
 join color as c on b.color_id = c.id
 join size as s on b.size_id = s.id
 ) as b2
where a.item_code = b2.item_code
and a.color = b2.color_code
and a.size = b2.size_code
)
```
```
追記
---
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の結果だけがおかしいみたいです。
謎です。。
  • MySQL

    10258 questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

1 SQLを追記

msx2

msx2 score 164

2017/04/08 10:42  投稿

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
select
 a.item_code,
 a.color,
 a.size,
 b2.id
from table_a as a
left join (
 select
   b.id
   i.item_code,
   c.color_code,
   s.size_code
 from table_b as b
 join item as i on b.item_id = i.id
 join color as c on b.color_id = c.id
 join size as s on b.size_id = s.id
 ) as b2
on (a.item_code = b2.item_code
and a.color = b2.color_code
and 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
select
 a.item_code,
 a.color,
 a.size
from table_a as a
where not exists ( select * from
(select
   b.id
   i.item_code,
   c.color_code,
   s.size_code
 from table_b as b
 join item as i on b.item_id = i.id
 join color as c on b.color_id = c.id
 join size as s on b.size_id = s.id
 ) as b2
where a.item_code = b2.item_code
and a.color = b2.color_code
and a.size = b2.size_code
)
```
  • MySQL

    10258 questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る