MySQL 結合: 内側、外側、左、右、十字

結合とは何ですか?

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

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

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

以下の例はすべて、これら 2 つの表を使用します。 映画ID の列 メンバー を指す id の列 映画 — 各JOINが一致する関係。

メンバー

映画

なぜJOINを使うべきなのでしょうか？

各JOINタイプを見る前に、複数のクエリを実行するよりもJOINが好まれる理由を知っておくと良いでしょう。

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

私たちがより良い成果を達成できることは明らかです MySQL JOIN を使用したアプリケーションのパフォーマンス。

JOINの種類

MySQL 複数のJOINタイプをサポートしており、それぞれが同じ2つのテーブルに関する異なる質問に答えます。以下の表はそれらを比較したもので、各タイプについてクエリとその出力例を示しています。

クロスジョイン

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

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

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

SELECT * FROM `movies` CROSS JOIN `members`

上記のスクリプトを実行すると、 MySQL 作業台 次の結果が得られます。

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`

外部結合

INNER JOINは、対応する行がない行を自動的に削除します。しかし、それらの行が重要な場合は、OUTER JOINが適切な選択肢となります。

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 ワークベンチでは次の結果が得られます。

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」句

これまでのクエリはすべて、ON句を持つ行と一致しています。 MySQL 一致する列が同じ名前を持つ場合、より短い代替案を提供します。

上記の 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 (NAIST) と JOIN を使用した使用 他にもたくさん使えます MySQL のような条項 グループ化、 どこ そして次のような機能さえあります 和, AVG, etc.

よくあるご質問