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）	行動
6	セーフハウス(2012)	行動
7	GIA	18+
8	2009年締め切り	18+
9	ダーティー・ピクチャー	18+
10	マーリーと私	ロマンス

結合の種類

クロス結合

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

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 を使用するだけで、指定された条件を満たす両方のテーブルから行が返されます。

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

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

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

正しい参加

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

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

「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 そしてアプリケーション（ソフトウェア）。さらに、アプリケーション側でもさらに多くのデータ操作が必要になります。

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

まとめ

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

よろしければ：