MySQL接続クエリの原理と応用

概要

MySQL の最も強力な機能の 1 つは、データ取得を実行しながらテーブルを結合できることです。ほとんどの単一テーブルデータクエリでは、ニーズを満たすことができません。現時点では、1 つ以上のテーブルを接続し、いくつかの条件を使用して必要なデータをフィルター処理する必要があります。

MySQL の結合クエリを理解する前に、まずデカルト積の原理を理解しましょう。

データ準備

前のセクションのテーブルデータ (classes テーブルと students テーブルを含む) を引き続き使用します。

mysql> クラスから * を選択します。
+---------+------------+
| クラスID | クラス名 |
+---------+------------+
| 1 | 1年生、9年生|
| 2 | 9 年生、2 クラス |
| 3 | 3年生、3クラス |
| 4 | 3年生～4年生 |
+---------+------------+
4行セット

mysql> 学生から * を選択します。
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 19 | ララ | 51 | 0 |
+-----------+-------------+--------+---------+
9行セット

デカルト積

デカルト積: デカルト積とも呼ばれ、2 つのセット A と B を想定し、デカルト積はセット A の要素とセット B の要素間のあらゆる関連付けによって生成されるすべての可能な結果を表します。

たとえば、A に m 個の要素があり、B に n 個の要素がある場合、A と B の直積は m*n の結果を生成します。これは、2 つのセットの要素を任意の組み合わせでループすることと同じです。

SQL におけるカルテシアン積の実装はクロス結合です。すべての結合方法では、まず一時的な直積テーブルが生成されます。直積はリレーショナル代数の概念であり、2 つのテーブル内の各データ行の任意の組み合わせを表します。

したがって、上記の表は 4 (クラス表) * 9 (生徒表) = 36 のデータになります。

デカルト積構文形式:

 tname1、tname2、... から cname1、cname2、... を選択します。
 または
 tname1 から cname を選択し、tname2 を結合します [tname を結合...];

凡例には次のことが示されています:

上記 2 つのテーブルの実際の実行結果は次のとおりです。

mysql> クラス a、学生 b から * を選択し、 a.classid、b.studentid で順序付けします。
+---------+------------+------------+-----------+---------+---------+
| クラス ID | クラス名 | 学生 ID | 学生名 | スコア | クラス ID |
+---------+------------+------------+-----------+---------+---------+
| 1 | クラス 1、グレード 9 | 1 | ブランド | 97.5 | 1 |
| 1 | クラス 1、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 1 | クラス 1、グレード 9 | 3 | lyn | 96 | 1 |
| 1 | クラス 1、グレード 9 | 4 | sol | 97 | 1 |
| 1 | 1年生、9年生 | 7 | b1 | 81 | 2 |
| 1 | 1年生、9年生 | 8 | b2 | 82 | 2 |
| 1 | クラス 1、グレード 9 | 13 | C1 | 71 | 3 |
| 1 | クラス 1、グレード 9 | 14 | C2 | 72.5 | 3 |
| 1 | 1年生、9年生 | 19 | lala | 51 | 0 |
| 2 | クラス 2、グレード 9 | 1 | ブランド | 97.5 | 1 |
| 2 | クラス 2、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 2 | クラス 2、グレード 9 | 3 | lyn | 96 | 1 |
| 2 | クラス 2、グレード 9 | 4 | sol | 97 | 1 |
| 2 | 2年生、9年生 | 7 | b1 | 81 | 2 |
| 2 | クラス 2、グレード 9 | 8 | b2 | 82 | 2 |
| 2 | クラス 2、グレード 9 | 13 | C1 | 71 | 3 |
| 2 | クラス 2、グレード 9 | 14 | C2 | 72.5 | 3 |
| 2 | 2年生、9年生 | 19 | lala | 51 | 0 |
| 3 | 3年生、9年生 | 1 | ブランド | 97.5 | 1 |
| 3 | クラス 3、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 3 | 3年生、9年生 | 3 | lyn | 96 | 1 |
| 3 | 3年生、9年生 | 4 | sol | 97 | 1 |
| 3 | 3年生、9年生 | 7 | b1 | 81 | 2 |
| 3 | 3年生、9年生 | 8 | b2 | 82 | 2 |
| 3 | 3年生、9年生 | 13 | C1 | 71 | 3 |
| 3 | クラス 3、グレード 9 | 14 | C2 | 72.5 | 3 |
| 3 | 3年生、9年生 | 19 | lala | 51 | 0 |
| 4 | クラス 3-4 | 1 | ブランド | 97.5 | 1 |
| 4 | クラス 3-4 | 2 | ヘレン | 96.5 | 1 |
| 4 | クラス 3-4 | 3 | lyn | 96 | 1 |
| 4 | 3-4年生 | 4 | sol | 97 | 1 |
| 4 | 3-4年生 | 7 | b1 | 81 | 2 |
| 4 | 3-4年生 | 8 | b2 | 82 | 2 |
| 4 | クラス 3-4 | 13 | C1 | 71 | 3 |
| 4 | クラス 3-4 | 14 | C2 | 72.5 | 3 |
| 4 | 3-4年生 | 19 | ララ | 51 | 0 |
+---------+------------+------------+-----------+---------+---------+
36行セット

この種のデータは、私たちが望むものではありません。実際のアプリケーションでは、テーブルを結合するときに制限を追加して、本当に必要なデータをフィルタリングする必要があります。

主な接続クエリは、内部接続、左 (外部) 接続、右 (外部) 接続です。 1 つずつ見ていきましょう。

内部結合クエリ内部結合

構文形式:

 結合条件で tname1 から cname を選択し、 tname2 を内部結合します。
 または、結合条件で tname1 から cname を選択し、tname2 を結合します。
 または、tname1、tname2からcnameを選択します[結合条件];

注: デカルト積に基づいて結合条件が追加され、2 つのテーブルが結合され、結合条件を満たすレコードが返されます。つまり、2 つのテーブルの交差部分 (網掛け部分) が返されます。この結合条件が追加されない場合、結果は上記の直積になります。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、 students b を a.classid = b.classid で内部結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
+-----------+-------------+--------+
8行セット

上記のデータから、3年生と4年生のクラスのクラスIDは4であり、関連付けられた生徒がいないため除外されていることがわかります。また、lalaのクラスIDは0であり、特定のクラスに関連付けることができないため、これも除外されています。両方のテーブルのデータ交差のみが取得されます。

mysql> クラス a、学生 b から a.classname、b.studentname、b.score を選択します。ここで、a.classid = b.classid かつ a.classid=1 です。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
+-----------+-------------+--------+
4行セット

クラス 1 の生徒の成績情報を検索するには、上記の 3 番目の構文形式を使用します。この方法は簡潔かつ効率的であり、Where 条件は接続クエリの結果の後にすぐにフィルター処理されます。

左結合クエリ左結合

左結合オン / 左外部結合オン、構文形式:

結合条件で tname1 から cname を選択し、tname2 を左結合します。

注: left join は left outer join の略語で、正式名称は left outer join であり、外部結合の一種です。左 (外部) 結合の場合、左側のテーブル (クラス) のすべてのレコードが表示され、右側のテーブル (生徒) には検索条件を満たすレコードのみが表示されます。右テーブルに関連付けることができないコンテンツはすべて null です。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、左側の students b を a.classid = b.classid で結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
| クラス 3-4 | NULL | NULL |
+-----------+-------------+--------+
9行セット

上記の結果から、3 年生と 4 年生には対応する生徒が見つからないため、最後の 2 つのフィールドは null でマークされていることがわかります。

右結合クエリ

右結合オン / 右外部結合オン、構文形式:

結合条件で tname1 から cname を選択し、tname2 を右結合します。

注: 右結合は右外部結合の略語であり、正式名称は右外部結合で、外部結合の一種です。左 (外部) 結合とは対照的に、右 (外部) 結合では、検索条件を満たす左のテーブル (クラス) のレコードのみが表示され、右のテーブル (生徒) のすべてのレコードが表示されます。左の表のレコードが不足している箇所はすべて NULL です。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、右側の students b を a.classid = b.classid で結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
| NULL | ララ | 51 |
+-----------+-------------+--------+
9行セット

上記の結果から、Lala はクラスを見つけることができないため、クラス名フィールドは null であることがわかります。

結合クエリ + 集計関数

結合クエリを使用する場合、データを要約するために集計関数がよく使用されます。たとえば、上記のデータに基づいて、各クラスの生徒数、平均スコア、クラスの合計スコアを照会できます。

mysql> a.classname を 'クラス名'、count(b.studentid) を '生徒総数'、sum(b.score) を '合計スコア'、avg(b.score) を '平均スコア' として選択します。
クラス A から学生 B を a.classid = b.classid で内部結合します。
a.classid、a.classname でグループ化します。
+----------+--------+---------+-----------+
| クラス名| 生徒総数| 合計点数| 平均点|
+----------+--------+---------+-----------+
| クラス 1、グレード 9 | 4 | 387.00 | 96.750000 |
| 2年生、9年生 | 2 | 163.00 | 81.500000 |
| 3年生、9年生 | 2 | 143.50 | 71.750000 |
+----------+--------+---------+-----------+
3行セット

ここでテーブルをクエリすると、クラス (classid、classname) がグループ化され、各クラスの生徒数、平均スコア、合計スコアが出力されます。

接続クエリの追加フィルター条件

接続クエリを使用した後、データはフィルタリングされる可能性が高いため、接続クエリの後に where 条件を追加できます。たとえば、上記の結果に基づいて、1 つのクラスの学生情報のみを取得できます。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、 students b を a.classid = b.classid で内部結合し、 a.classid=1 にします。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
+-----------+-------------+--------+
4行セット

上記のように、1 つのクラスの生徒のみが出力されます。同様に、制限制約、並べ替えによる順序付け、その他の操作を追加できます。

要約する

1. 結合クエリには結合条件が含まれている必要があります。含まれていない場合は、直交積データになります。結合条件が間違っていると、間違ったデータが返されます。

2. SQL 仕様では、INNER JOIN 構文を第一の選択肢として使用することが推奨されています。ただし、複数の接続方法自体には明らかなパフォーマンスの違いはありません。パフォーマンスの違いは、主にデータ構造、接続条件、インデックスの使用状況など、複数の条件の組み合わせによって決まります。

上記のデータシナリオなど、実際のビジネスシナリオに基づいて決定する必要があります。クラスと学生を返す必要がある場合は、内部結合を使用します。すべてのクラスを出力する必要がある場合は、左結合を使用します。すべての学生を出力する必要がある場合は、右結合を使用します。

3. パフォーマンスの考慮事項: MySQL は実行時に関連条件に従って接続されたテーブルを処理します。この処理はリソースを大量に消費する可能性があります。接続されるテーブルが多いほど、パフォーマンスの低下は深刻になります。そのため、不要な接続や表示する必要がないフィールドを分析して削除する必要があります。

以前、私のプロジェクトチームが古いビジネスコードを最適化していたとき、ビジネスが変化するにつれて、一部のデータは表示する必要がなくなり、対応する接続も不要になったことがわかりました。それを削除した後、パフォーマンスが大幅に向上しました。

上記は、MySQL 接続クエリの原理と応用の詳細な内容です。MySQL 接続クエリの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: