MySQL の無効な左結合の問題を解決する方法とその使用上の注意

MySQLの左結合が無効であり、その使用方法

今日SQLを書いていたとき、左結合を使用すると左のテーブルからすべてのデータが取得されないことに気づき、しばらく落ち込んでいました。後でよく調べたところ、常識的な間違いを犯していたことに気付きました（私は初心者です）

これは元のSQLです

このクエリでは tb_line テーブル内のすべてのデータを取得できません。残念です...

後で、複数テーブルのクエリを実行すると、where ステートメントを実行する前に一時テーブルが形成されることを知りました。

オンは一時テーブルでの条件付きスクリーニングです。左結合を使用すると、条件が真であるかどうかに関係なく、左テーブルのデータが照会されます。条件が偽の場合は、null として表示されます。

一時テーブルが生成された後のフィルタリング条件はどこにありますか

最初の図では、tb_vehicle テーブルのフィルター条件を where 句に指定しています。左結合によって生成された false 条件のデータは、where 句 v.del_flag='0' でフィルター処理されます (false 条件のデータでは del_flag が空であるため)。

ということで、左結合を使用したように見えますが、実際にはこのように記述した結果は、内部結合を使用した場合と同じになります。

正しい SQL は次のとおりです。

一時テーブルでは、条件をフィルタリングして、左側のテーブルのデータを取得できます。

要約:

左結合を使用して条件クエリを実行する必要がある場合は、条件フィルターを on の後に配置するか where の後に配置するかを慎重に検討する必要があります。

MySQL 左結合の落とし穴回避ガイド

現象

LEFT JOIN は、ブログの記事にコメントがいくつあるか、ショッピングモールの商品にコメントがいくつあるか、コメントにいいねがいくつあるかなど、MySQL を使用してクエリを実行するときに非常によく使用されます。ただし、join、on、where などのキーワードに馴染みがないために、クエリ結果が期待どおりにならない場合があります。そこで、今日はそれらをまとめて、落とし穴を回避できるようにします。

ここでシナリオを示し、2 つの質問をします。正しく答えられる場合は、この記事を読む必要はありません。

クラス管理アプリケーションがあり、すべてのクラスを格納するテーブル classes と、すべての生徒を格納するテーブル students があるとします。具体的なデータは次のとおりです。

クラスから * を選択;

学生から*を選択します。

現在、2 つの要件があります。

各クラスの名前とそれに対応する女子生徒の数を調べます
クラスの生徒総数を調べる

要件 1 については、ほとんどの人が何も考えずに次の 2 つの SQL の書き方を思いつくでしょう。どちらが正しいでしょうか。

c.name を選択し、count(s.name) を num として計算します。 
    クラスcから左の生徒sに参加 
    s.class_id = c.idの場合 
    かつ s.gender = 'F'
    c.name でグループ化

または

c.name を選択し、count(s.name) を num として計算します。 
    クラスcから左の生徒sに参加 
    s.class_id = c.idの場合 
    ここで、s.gender = 'F'
    c.name でグループ化

要件 2 については、ほとんどの人が何も考えずに次の 2 つの SQL の書き方を思いつくでしょう。どちらが正しいでしょうか。

c.name を選択し、count(s.name) を num として計算します。 
    クラスcから左の生徒sに参加 
    s.class_id = c.idの場合 
    c.name = 'クラス 1' 
    c.name でグループ化

または

c.name を選択し、count(s.name) を num として計算します。 
    クラスcから左の生徒sに参加 
    s.class_id = c.idの場合 
    c.name = 'クラス 1' 
    c.name でグループ化

下にスクロールしないでください。！まずあなた自身の答えを述べてください。正解は下にあります。

〜

答えは、最初のステートメントが両方の要件に対して正しいということです。この問題を理解するには、次のセクションで詳しく説明する MySQL の左結合の実行原理を理解する必要があります。

ソース

MySQL は、左結合を処理するためにネストされたループアプローチを使用します。次のステートメントを例に挙げます。

LT LEFT から * を選択し、RT を P1(LT,RT) に結合します。P2(LT,RT) が P2(LT,RT) であるかどうかを確認します。

P1 は on フィルタ条件であり、存在しない場合は TRUE とみなされます。P2 は where フィルタ条件であり、存在しない場合は TRUE とみなされます。このステートメントの実行ロジックは次のように記述できます。

FOR each row lt in LT{// 左側のテーブルの各行を走査します BOOL b = FALSE;
  FOR RT の各行 rt で P1(lt, rt) を満たす場合、{// 右側のテーブルの各行を走査し、結合条件を満たす行を検索します。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 は最適化のためにバッファを使用し、行の比較回数を減らしますが、これはキー実行プロセスには影響せず、この記事の範囲外です。

この疑似コードから、次の 2 つの点がわかります。

右側のテーブルを制限する場合は、on 条件で行う必要があります。where 条件で行うと、データが欠落し、右側のテーブルに一致する行がない左側のテーブルの行が最終結果に表示されない可能性があります。これは、左結合の理解に反します。左のテーブルの行のうち右のテーブルに一致する行がない場合は、右のテーブルをトラバースした後、b=FALSE になるため、右のテーブルを NULL で埋めようとします。ただし、この時点では、P2 によって右のテーブルの行が制限されます。NULL が P2 を満たさない場合 (NULL は、IS NULL でない限り、通常、制限条件を満たしません)、最終結果に追加されず、結果が欠落することになります。

where 条件がない場合、on 条件が左のテーブルにどのような制限を課すかに関係なく、左のテーブルの各行には少なくとも 1 行の合成結果が含まれます。左のテーブルの行の場合、右のテーブルに対応する行がない場合は、右のテーブルのトラバーサルが完了した後に b=FALSE になり、NULL の行を使用してデータが生成されますが、これは冗長です。したがって、左側のテーブルをフィルター処理するには where を使用する必要があります。

2 つの要件に対するエラーステートメントの実行結果とエラーの原因は次のとおりです。

要件 1