MySQLのLEFT JOINピット回避ガイド

げんしょうleft joinmysqlを使って検索する過程でよく見られることです.例えば、ブログの文章に何件のコメントがあるか、ショッピングモールの商品に何件のコメントがあるか、1件のコメントに何人のコメントがあるかなどです.しかしjoin・on・where・などのキーワードに慣れていないため、検索結果が予想に合わない場合があるので、今日はまとめて、一緒にピットを避けていきます.
ここで私はまず1つのシーンを提供して、2つの問題を投げ出して、もしあなたがその文章に答えることができたら、読む必要はありません.
クラス管理アプリケーションがあり、テーブルclassesがあり、すべてのクラスが保存されていると仮定します.すべての学生を保存した表studentsがあります.具体的なデータは以下の通りです(廖雪峰のオンラインSQLに感謝します).SELECT * FROM classes;

id    name
1      
2      
3      
4      

SELECT * FROM students;

id  class_id  name   gender
1    1                  M
2    1                  F
3    1                  M
4    1                  F
5    2                  F
6    2                  M
7    2                  M
8    3                  F
9    3                  M
10    3                  F

では、2つのニーズがあります.

クラスごとの名称とそれに対応する女子生徒数を探し出す

クラスを見つけた人の総数

需要1については、多くの人が考えずに次の2つのsqlの書き方を考え出すことができますが、どちらが正しいですか?

SELECT c.name, count(s.name) as num 
    FROM classes c left join students s 
    on s.class_id = c.id 
    and s.gender = 'F'
    group by c.name

または

SELECT c.name, count(s.name) as num 
    FROM classes c left join students s 
    on s.class_id = c.id 
    where s.gender = 'F'
    group by c.name

需要2に対して、多くの人も考えずに次の2つのsqlの書き方を考え出すことができますが、どちらが正しいですか?

SELECT c.name, count(s.name) as num 
    FROM classes c left join students s 
    on s.class_id = c.id 
    where c.name = '  ' 
    group by c.name

または

SELECT c.name, count(s.name) as num 
    FROM classes c left join students s 
    on s.class_id = c.id 
    and c.name = '  ' 
    group by c.name

下にひっくり返さないでください!!まず自分の答えを出して、正しい答えは下にあります.......答えは両方とも第一の文が正しいということですが、この問題を明らかにするには、mysqlがleft joinの実行原理について、次の節で展開することを理解しなければなりません.
ルーツ
mysqlはleft joinのネストループのような方法でスレーブ処理を行い、以下の文を例に挙げます.

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

このうちP1Yesonフィルタ条件、欠落はTRUE、P2Yeswhereフィルタ条件、欠落もTRUE、この文の実行ロジックは以下のように記述できる.

FOR each row lt in LT {//         
  BOOL b = FALSE;
  FOR each row rt in RT such that P1(lt, rt) {//        ，    join    
    IF P2(lt, rt) {//   where     
      t:=lt||rt;//   ，    
    }
    b=TRUE;// lt RT      
  }
  IF (!b) { //    RT，  lt RT        ，    null   
    IF P2(lt,NULL) {//   null    where     
      t:=lt||NULL; //   lt null    
    }         
  }
}

もちろん、実際にはMySQLはbuffer方式で最適化され、ローの比較回数が減少しますが、重要な実行プロセスには影響しません.本明細書では説明しません.
この擬似コードから、2つの点がわかります.

右の表を制限したい場合は必ずon条件で行い、whereで行うとデータが欠落してしまい、左の表が右の表で一致行がない行が最終結果に出なくなる可能性があり、私たちのleft joinに対する理解に反します.左の表に右の表がない行については、右の表の後b=FALSEを遍歴するので、NULL右の表を足し合わせようとしますが、このとき私たちのP2右の表の行には制限があり、NULLがP2(NULL一般的には制限条件を満たさないので、IS NULLこのような場合を除く)では、最終的な結果には入れません.結果が欠落します.

ない場合where条件は、on条件が左テーブルに対してどのような制限を加えても、左テーブルの各行に少なくとも1行の合成結果があり、左テーブル行に対して右テーブルに対応する行がなければ、右テーブル遍歴終了後b=FALSE1行NULLでデータが生成されるが、このデータは余計である.したがって、左のテーブルをフィルタするにはwhereを使用する必要があります.

次の2つの要件のエラー文の実行結果とエラーの原因を展開します:要件1

name    num
      2
      1
      2

需要2

name    num
      4
      0
      0
      0

デマンド1 where条件で右表が制限されているためデータが欠落している(四班では0の結果があるはず)

需要2 on条件で左の表に制限があるためデータが余っている(他のクラスの結果も出ているが、間違っている)

まとめ
以上の問題現象と分析から,left join文では左表フィルタはwhere条件,右表フィルタはon条件でなければならず,結果は少なく,ちょうどいいと結論した.
SQLは简単に见えて、実は多くの细部の原理が中にあって、1つの小さい混同は结果が予想と一致しないことをもたらして、だからふだんこれらの细部の原理に注意して、肝心な时に间违いを避けます.