MYSQL5.5でSQLの差集合について教えてください。


下記のサイトでLEFT OUTER JOINの左側だけいっときますは理解できます。
http://www.ideaxidea.com/archives/2011/01/sql_join_visually_explained.html

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

TABLE A
MIKE
JOHN
KEN

TABLE B
MIKE
JOHN

の場合、MIKE, JOHN, KENが抽出されるのはわかります。

問題は、TABLE A - TABLE Bがわかりません。

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

「WHERE TableB.id IS null」なぜこの条件が共通部分だけ除くのかが理解できません。

わかりやすくお教え頂けないでしょうか?よろしくお願いします。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/07/04 19:32:34
  • 終了:2011/07/11 19:35:06

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/07/06 18:48:11

ポイント50pt

【考え方その1】SQL初心者向け

まずはWHERE条件が無い場合にどのような結果になるかを考えることです

SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name

表1

TableA.nameTableB.nameTableB.id
MIKEMIKEMIKEのID
JOHNJOHNJOHNのID
KEN  

データベースでは何も無いという状態をNull(SQL中に書く場合はnullかNULLが一般的)と言いますので表を書き直すと下記になります

表2

TableA.nameTableB.nameTableB.id
MIKEMIKEMIKEのID
JOHNJOHNJOHNのID
KENnullnull

 

表2の状態にWHERE条件を加えると…

SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableB.id IS null

答えとしては下記のようにTableBのidが存在しない人だけが抽出されます

表3

TableA.nameTableB.nameTableB.id
KENnullnull

 

【ワンポイント】

WHERE TableB.name IS nullではなくWHERE TableB.id IS nullなのは「TableBに同じnameがあった場合でもidに何も情報が入っていない(null)場合は未登録扱いにしたい(処理を分けたい)」といったような場面が想定されます

 

【考え方その2】中級以上向け

SQLがどのように解釈され、そして動いていくのかを学ぶとスキルは飛躍的に向上するのですが、結構難解なので、ひとまず簡略化してみました

処理される順番SQL意訳
1FROM TableATableAをまずは見なさい
2LEFT OUTER JOIN TableB ON TableA.name = TableB.nameTableAを見ながら、TableBを見て同じnameの人を選びだしなさい
3WHERE TableB.id IS nullid未登録の人だけにしなさい
4SELECT *結果を表示しなさい

 

【ワンポイント】

TableBのnameにインデックスが無い状態では、TableAを1レコード処理するごとにTableBの1万レコードすべてと比較して同じnameの人を探すことになり、仮にTableBの1レコード目で見つかった場合でも、検索をそこでやめたりはせず、常に1万レコードすべてに目を通しますので、1万x10万という膨大な処理になります

TableBのnameでインデックス(見出し)があれば、見出しだけを見て存在するかどうかや、存在した場合にどこにデータがあるのかが即座に判るため、処理速度は飛躍的に向上します(最近のパソコンなら数秒、長くても数分で終わるはずです)

ちなみに、何でもかんでもインデックスをつければ高速化するというものではなく、今回のTableBのnameのように「有ると無しでは大違い」の場合にのみ付けるものと覚えておいてください

id:popattack

1つ1つの詳しいステップを書いて頂きありがとうございます!大変わかりやすく、理解できました!本当にありがとうございます!

2011/07/09 10:52:24

その他の回答(1件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912011/07/05 00:25:20

ポイント50pt

参考のページでよく説明されていると思いますが、

Outer Left Join => TableA ですから、そこから TableB( TableB.ID Is Null)

には無いものを選択したら、TableA だけになるということで理解できないでしょうか。


20110705001331


上の図で言えば一番上の行(TableB にない=TableAだけにある)ものを

抽出しているのがご質問のクエリ文です。


実行時間に関しては、いろいろな要因があると思いますが、まずは Count で

該当件数を調べてみてはどうでしょうか。

結果が多すぎると、出力に時間がかかったり、失敗する例もあります。


処理速度を改善するためには、条件部分にインデックスを使用することで

改善が見込めます。

http://itpro.nikkeibp.co.jp/article/COLUMN/20060111/227105/

http://itpro.nikkeibp.co.jp/article/COLUMN/20060111/227105/?SS=imgview&FD=8179535&ST=develop

id:popattack

わかりやすい表まで作っていただきありがとうございます!!理解できました!ありがとうございます!

2011/07/09 10:51:54
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/07/06 18:48:11ここでベストアンサー

ポイント50pt

【考え方その1】SQL初心者向け

まずはWHERE条件が無い場合にどのような結果になるかを考えることです

SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name

表1

TableA.nameTableB.nameTableB.id
MIKEMIKEMIKEのID
JOHNJOHNJOHNのID
KEN  

データベースでは何も無いという状態をNull(SQL中に書く場合はnullかNULLが一般的)と言いますので表を書き直すと下記になります

表2

TableA.nameTableB.nameTableB.id
MIKEMIKEMIKEのID
JOHNJOHNJOHNのID
KENnullnull

 

表2の状態にWHERE条件を加えると…

SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableB.id IS null

答えとしては下記のようにTableBのidが存在しない人だけが抽出されます

表3

TableA.nameTableB.nameTableB.id
KENnullnull

 

【ワンポイント】

WHERE TableB.name IS nullではなくWHERE TableB.id IS nullなのは「TableBに同じnameがあった場合でもidに何も情報が入っていない(null)場合は未登録扱いにしたい(処理を分けたい)」といったような場面が想定されます

 

【考え方その2】中級以上向け

SQLがどのように解釈され、そして動いていくのかを学ぶとスキルは飛躍的に向上するのですが、結構難解なので、ひとまず簡略化してみました

処理される順番SQL意訳
1FROM TableATableAをまずは見なさい
2LEFT OUTER JOIN TableB ON TableA.name = TableB.nameTableAを見ながら、TableBを見て同じnameの人を選びだしなさい
3WHERE TableB.id IS nullid未登録の人だけにしなさい
4SELECT *結果を表示しなさい

 

【ワンポイント】

TableBのnameにインデックスが無い状態では、TableAを1レコード処理するごとにTableBの1万レコードすべてと比較して同じnameの人を探すことになり、仮にTableBの1レコード目で見つかった場合でも、検索をそこでやめたりはせず、常に1万レコードすべてに目を通しますので、1万x10万という膨大な処理になります

TableBのnameでインデックス(見出し)があれば、見出しだけを見て存在するかどうかや、存在した場合にどこにデータがあるのかが即座に判るため、処理速度は飛躍的に向上します(最近のパソコンなら数秒、長くても数分で終わるはずです)

ちなみに、何でもかんでもインデックスをつければ高速化するというものではなく、今回のTableBのnameのように「有ると無しでは大違い」の場合にのみ付けるものと覚えておいてください

id:popattack

1つ1つの詳しいステップを書いて頂きありがとうございます!大変わかりやすく、理解できました!本当にありがとうございます!

2011/07/09 10:52:24
  • id:popattack
    SELECT * FROM TableA
    LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name
    WHERE TableB.id IS null
    を参考に10万件のデータ(TABLE A)と1万件のデータ(TABLE B)を差集合として
    出そうとしていますが、4時間たった今でも処理が終わりません・・・
    差集合の結果を得るのには時間がかかるのでしょうか?

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません