MySQL の on と where における左結合設定条件の使用法の違いの分析

この記事では、MySQL の左結合における on 条件と where 条件の使用法の違いを例を使って説明します。ご参考までに、詳細は以下の通りです。

1. まず、テスト用に 2 つのテーブルを準備します。

テーブル `a` を作成します (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) デフォルト '' コメント 'name',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

テーブル `b` を作成します (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `a_id` int(11) デフォルト '0' コメント 'テーブルID',
 `name` varchar(32) デフォルト '' コメント 'name',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

2 つのテーブルのデータは図に示されています。

次の左結合クエリを実行します。

a の左から * を選択し、b を a.id = b.a_id で結合します。

実行結果が同じかどうかを確認するために、それぞれ on と where の後に条件を追加します。

a から * を選択し、a.id = b.a_id かつ b.id > 3 で b を左結合します。

a の左から * を選択し、b を a.id = b.a_id で結合します (b.id > 3)。

上記の 2 つのステートメントでは、設定した条件は同じで、両方とも b.id > 3 ですが、表示される結果が異なるのはなぜでしょうか。

SQL文のクエリのキーワードシーケンスは、一般的には > where > group by > having > order by となります。

左結合が from 範囲内にある場合、on 条件は最初に左結合の右側のテーブルをフィルターし、次に where 条件の結果をフィルターします。

複数の左結合により、一時テーブルが生成されます。on 条件は左結合の右テーブルをフィルター処理し、where 条件は最後に生成された一時テーブルをフィルター処理します。

それで：

on の後に条件 b.id > 3 が記述されている場合、最初に右側のテーブル (関連テーブル) をスクリーニングして条件を満たす行を取得し、次にメイン テーブルを左結合してメイン テーブルのすべての行を返します。右側のテーブルで一致しない行は null で表されます。

条件 b.id > 3 が where の後に記述されている場合、メイン テーブルは右側のテーブル (関連付けられたテーブル) と左結合されてすべての行が返され、その後 where 条件を使用して結果がフィルター処理されます。

注: on の後の条件は右側のテーブル (関連付けられたテーブル) に対するものであり、メイン テーブルには影響しません。

a の左から * を選択し、a.id = b.a_id かつ a.id > 3 で b を結合します。

後でメイン テーブルに条件 a.id > 3 を追加しましたが、メイン テーブルのすべてのデータは引き続き表示されますが、右側のテーブル (関連テーブル) の表示に影響します。

メインテーブルをフィルタリングする場合は、where の後に条件を記述する必要があります。

a の左から * を選択し、b を a.id = b.a_id で結合します (a.id > 3)。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキル」、および「MySQL データベース ロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。