MySQL JOINS チュートリアル: INNER、OUTER、LEFT、RIGHT、CROSS

結合とは何ですか?

結合は、XNUMX つ以上のデータベース テーブルからデータを取得するのに役立ちます。

テーブルは主キーと外部キーを使用して相互に関連付けられます。

注: JOIN は、SQL 学習者の間で最も誤解されているトピックです。 単純化して理解しやすくするために、サンプルの練習には新しいデータベースを使用します。 以下に示すように

id ファーストネーム 苗字 映画ID
1 アダム Smith 1
2 ラヴィ クマー 2
3 スーザン デビッドソン 5
4 ジェニー Adrianna 8
5 リー ポン 10
id タイトル カテゴリ
1 アサシン クリード: 残り火 アニメーション
2 リアルスティール(2012) アニメーション
3 アルビンとチップマンクス アニメーション
4 タンタンの冒険 アニメーション
5 安全(2012) Action
6 セーフハウス(2012) Action
7 GIA 18+
8 2009年締め切り 18+
9 ダーティー・ピクチャー 18+
10 マーリーと私 ロマンス

結合の種類

クロス結合

Cross JOIN は、あるデータベース テーブルの各行を別のデータベース テーブルのすべての行に一致させる、最も単純な形式の JOIN です。

言い換えれば、最初のテーブルの各行と XNUMX 番目のテーブルのすべてのレコードの組み合わせが得られます。

すべてのムービー レコードに対してすべてのメンバー レコードを取得したいとします。以下に示すスクリプトを使用して、目的の結果を得ることができます。

結合の種類

SELECT * FROM `movies` CROSS JOIN `members`

上記のスクリプトを実行すると、 MySQLワークベンチ フォローしてくださいwing 結果。

id title id first_name last_name movie_id
1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1
1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2
1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5
1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8
1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10
2 Real Steel(2012) Animations 1 Adam Smith 1
2 Real Steel(2012) Animations 2 Ravi Kumar 2
2 Real Steel(2012) Animations 3 Susan Davidson 5
2 Real Steel(2012) Animations 4 Jenny Adrianna 8
2 Real Steel(2012) Animations 6 Lee Pong 10
3 Alvin and the Chipmunks Animations 1 Adam Smith 1
3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2
3 Alvin and the Chipmunks Animations 3 Susan Davidson 5
3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8
3 Alvin and the Chipmunks Animations 6 Lee Pong 10
4 The Adventures of Tin Tin Animations 1 Adam Smith 1
4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2
4 The Adventures of Tin Tin Animations 3 Susan Davidson 5
4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8
4 The Adventures of Tin Tin Animations 6 Lee Pong 10
5 Safe (2012) Action 1 Adam Smith 1
5 Safe (2012) Action 2 Ravi Kumar 2
5 Safe (2012) Action 3 Susan Davidson 5
5 Safe (2012) Action 4 Jenny Adrianna 8
5 Safe (2012) Action 6 Lee Pong 10
6 Safe House(2012) Action 1 Adam Smith 1
6 Safe House(2012) Action 2 Ravi Kumar 2
6 Safe House(2012) Action 3 Susan Davidson 5
6 Safe House(2012) Action 4 Jenny Adrianna 8
6 Safe House(2012) Action 6 Lee Pong 10
7 GIA 18+ 1 Adam Smith 1
7 GIA 18+ 2 Ravi Kumar 2
7 GIA 18+ 3 Susan Davidson 5
7 GIA 18+ 4 Jenny Adrianna 8
7 GIA 18+ 6 Lee Pong 10
8 Deadline(2009) 18+ 1 Adam Smith 1
8 Deadline(2009) 18+ 2 Ravi Kumar 2
8 Deadline(2009) 18+ 3 Susan Davidson 5
8 Deadline(2009) 18+ 4 Jenny Adrianna 8
8 Deadline(2009) 18+ 6 Lee Pong 10
9 The Dirty Picture 18+ 1 Adam Smith 1
9 The Dirty Picture 18+ 2 Ravi Kumar 2
9 The Dirty Picture 18+ 3 Susan Davidson 5
9 The Dirty Picture 18+ 4 Jenny Adrianna 8
9 The Dirty Picture 18+ 6 Lee Pong 10
10 Marley and me Romance 1 Adam Smith 1
10 Marley and me Romance 2 Ravi Kumar 2
10 Marley and me Romance 3 Susan Davidson 5
10 Marley and me Romance 4 Jenny Adrianna 8
10 Marley and me Romance 6 Lee Pong 10

INNER JOINは

内部 JOIN は、指定された条件を満たす両方のテーブルから行を返すために使用されます。

映画をレンタルしたメンバーのリストと、そのメンバーがレンタルした映画のタイトルを取得したいとします。 これには INNER JOIN を使用するだけで、指定された条件を満たす両方のテーブルから行が返されます。

INNER JOINは

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

上記のスクリプトを実行すると、

first_name last_name title
Adam Smith ASSASSIN'S CREED: EMBERS
Ravi Kumar Real Steel(2012)
Susan Davidson Safe (2012)
Jenny Adrianna Deadline(2009)
Lee Pong Marley and me

上記の結果スクリプトは、次のように記述しても同じ結果が得られることに注意してください。

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members`AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

外部結合

MySQL の外部結合は、両方のテーブルから一致するすべてのレコードを返します。

結合されたテーブル内で一致しないレコードを検出できます。 戻ります NULL 一致するものが見つからなかった場合は、結合テーブルのレコードの値。

わかりにくいですか? 例を見てみましょう –

左結合します

ここで、すべての映画のタイトルと、それをレンタルしたメンバーの名前を取得したいとします。 誰からもレンタルされていない映画があることは明らかです。 単純に使用できます 左結合します 目的のために。

外部結合

LEFT JOIN は、右側のテーブルに一致する行が見つからない場合でも、左側のテーブルのすべての行を返します。 右側のテーブルに一致するものが見つからない場合は、NULL が返されます。

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

MySQL ワークベンチで上記のスクリプトを実行すると、レンタルされていない映画の結果の下にリストされている返された結果で、メンバー名フィールドに NULL 値が含まれていることがわかります。 これは、特定の映画のメンバー テーブルに一致するメンバーが見つからなかったことを意味します。

title first_name last_name
ASSASSIN'S CREED: EMBERS Adam Smith
Real Steel(2012) Ravi Kumar
Safe (2012) Susan Davidson
Deadline(2009) Jenny Adrianna
Marley and me Lee Pong
Alvin and the Chipmunks NULL NULL
The Adventures of Tin Tin NULL NULL
Safe House(2012) NULL NULL
GIA NULL NULL
The Dirty Picture NULL NULL
Note: Null is returned for non-matching rows on right

正しい参加

RIGHT JOIN は明らかに LEFT JOIN の逆です。 RIGHT JOIN は、左側のテーブルで一致する行が見つからなかった場合でも、右側のテーブルのすべての列を返します。 左側のテーブルに一致するものが見つからない場合は、NULL が返されます。

この例では、メンバーの名前とメンバーがレンタルしている映画を取得する必要があると仮定します。 まだ映画をレンタルしていない新しいメンバーが加わりました

正しい参加

SELECT  A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

MySQL ワークベンチで上記のスクリプトを実行すると、次のようになります。wing 結果。

first_name last_name title
Adam Smith ASSASSIN'S CREED: EMBERS
Ravi Kumar Real Steel(2012)
Susan Davidson Safe (2012)
Jenny Adrianna Deadline(2009)
Lee Pong Marley and me
NULL NULL Alvin and the Chipmunks
NULL NULL The Adventures of Tin Tin
NULL NULL Safe House(2012)
NULL NULL GIA
NULL NULL The Dirty Picture
Note: Null is returned for non-matching rows on left

「ON」句と「USING」句

上記の JOIN クエリの例では、ON 句を使用してテーブル間のレコードを照合しました。

USING 句も同じ目的に使用できます。 との違い 使用する それです 両方のテーブルで一致する列の名前が同じである必要があります。

これまで「movies」テーブルでは、「id」という名前の主キーを使用していました。 「members」テーブルでは「movie_id」という名前で同じものを参照しました。

「movies」テーブルの「id」フィールドの名前を「movie_id」という名前に変更しましょう。 これは、一致するフィールド名が同一になるようにするためです。

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

次に、上記の LEFT JOIN の例で USING を使用してみましょう。

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

使用から離れて ON > JOIN を使用した使用 他の多くの MySQL 句を使用できます。 どこでグループ化するか そして次のような機能さえあります , AVG, etc.

なぜ結合を使用する必要があるのでしょうか?

同じタスクを実行してクエリを実行できるのに、なぜ JOIN を使用するのか疑問に思われるかもしれません。 特にデータベース プログラミングの経験がある場合は、クエリを XNUMX つずつ実行し、それぞれの出力を連続したクエリで使用できることをご存知でしょう。 もちろん、それは可能です。 ただし、JOIN を使用すると、任意の検索パラメーターを含む XNUMX つのクエリだけを使用して作業を完了できます。 一方で MySQL はより優れたパフォーマンスを達成できる インデックス作成を使用できるため、JOIN を使用します。 複数のクエリを実行する代わりに単一の JOIN クエリを使用するだけで、サーバーのオーバーヘッドが削減されます。 代わりに複数のクエリを使用すると、MySQL とアプリケーション (ソフトウェア) 間のデータ転送が増加します。 さらに、アプリケーション側でもさらに多くのデータ操作が必要になります。

JOIN を使用すると、MySQL とアプリケーションのパフォーマンスを向上できることは明らかです。

まとめ

  • JOINS を使用すると、複数のテーブルのデータを XNUMX つの結果セットに結合できます。
  • JOINS はサブクエリと比較してパフォーマンスが優れています
  • INNER JOINS は、指定された基準を満たす行のみを返します。
  • OUTER JOINS は、一致が見つからなかった行を返すこともできます。 一致しない行は、NULL キーワードを使用して返されます。
  • 主な結合タイプには、内部結合、左外部結合、右外部結合、交差結合などが含まれます。
  • JOIN 操作でよく使用される句は「ON」です。 「USING」句では、一致する列が同じ名前であることが必要です。
  • JOINS は、GROUP BY、WHERE、SUB QUERIES、AGGREGATE FUNCTIONS などの他の句でも使用できます。