MySQL การเชื่อมต่อ: ด้านใน, ด้านนอก, ด้านซ้าย, ด้านขวา, ไขว้

JOINS คืออะไร?

รวมความช่วยเหลือในการดึงข้อมูลจากตารางฐานข้อมูลตั้งแต่สองตารางขึ้นไป

ตารางมีความสัมพันธ์กันโดยใช้คีย์หลักและคีย์นอก

หมายเหตุ: คำสั่ง JOIN เป็นหัวข้อที่ผู้เรียน SQL เข้าใจผิดมากที่สุด เพื่อความเรียบง่ายและเข้าใจง่าย เราจะใช้ฐานข้อมูลใหม่เป็นตัวอย่างในการฝึกฝน ดังแสดงด้านล่าง

ตัวอย่างทุกตัวอย่างด้านล่างนี้ใช้ตารางสองตารางนี้ movie_id คอลัมน์ใน สมาชิก ชี้ไปที่ id คอลัมน์ใน ภาพยนตร์ — ความสัมพันธ์ที่แต่ละ JOIN จับคู่กัน

สมาชิก

ภาพยนตร์

เหตุใดเราจึงควรใช้ JOINS?

ก่อนที่จะพิจารณาประเภทของ JOIN แต่ละประเภท เราควรทราบก่อนว่าทำไมการใช้ JOIN จึงดีกว่าการเรียกใช้คำสั่ง SQL หลายๆ คำสั่ง

ตอนนี้คุณอาจคิดว่าเหตุใดเราจึงใช้ JOIN ในเมื่อเราสามารถทำงานเดียวกันกับที่เรียกใช้แบบสอบถามได้ โดยเฉพาะอย่างยิ่งหากคุณมีประสบการณ์ในการเขียนโปรแกรมฐานข้อมูลมาบ้าง คุณรู้ว่าเราสามารถเรียกใช้คิวรีได้ทีละรายการ ให้ใช้เอาต์พุตของแต่ละรายการในการสืบค้นที่ต่อเนื่องกัน แน่นอนว่ามันเป็นไปได้ แต่การใช้ JOIN คุณสามารถทำงานให้สำเร็จได้โดยใช้เพียงคิวรีเดียวกับพารามิเตอร์การค้นหาใดๆ ในทางกลับกัน MySQL สามารถบรรลุประสิทธิภาพที่ดีขึ้น ด้วย JOIN เนื่องจากสามารถใช้การจัดทำดัชนีได้ เพียงใช้แบบสอบถาม JOIN เดียวแทนการเรียกใช้หลายแบบสอบถามจะช่วยลดค่าใช้จ่ายของเซิร์ฟเวอร์ การใช้แบบสอบถามหลายรายการแทนซึ่งจะทำให้มีการถ่ายโอนข้อมูลระหว่างกันมากขึ้น MySQL และแอพพลิเคชั่น (ซอฟต์แวร์) นอกจากนี้ยังต้องมีการจัดการข้อมูลเพิ่มเติมในส่วนท้ายของแอปพลิเคชันด้วย

เป็นที่ชัดเจนว่าเราสามารถประสบความสำเร็จได้ดีขึ้น MySQL และประสิทธิภาพของแอปพลิเคชันโดยใช้ JOIN

ประเภทของข้อต่อ

MySQL รองรับการเชื่อมต่อข้อมูลหลายประเภท (JOIN) ซึ่งแต่ละประเภทจะตอบคำถามที่แตกต่างกันเกี่ยวกับตารางสองตารางเดียวกัน ตารางด้านล่างเปรียบเทียบประเภทต่างๆ เหล่านั้น จากนั้นจะสาธิตการใช้งานแต่ละประเภทด้วยตัวอย่างคำสั่ง SQL และผลลัพธ์

ข้ามเข้าร่วม

Cross JOIN เป็นรูปแบบที่ง่ายที่สุดของ JOIN ซึ่งจับคู่แต่ละแถวจากตารางฐานข้อมูลหนึ่งไปยังทุกแถวของอีกตารางหนึ่ง

กล่าวอีกนัยหนึ่งทำให้เราสามารถรวมแต่ละแถวของตารางแรกเข้ากับบันทึกทั้งหมดในตารางที่สองได้

สมมติว่าเราต้องการรับบันทึกสมาชิกทั้งหมดเทียบกับบันทึกภาพยนตร์ทั้งหมด เราสามารถใช้สคริปต์ที่แสดงด้านล่างเพื่อให้ได้ผลลัพธ์ที่ต้องการ

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

การเชื่อมแบบ Cross Join จะส่งคืนคู่ที่เป็นไปได้ทั้งหมด ซึ่งโดยส่วนใหญ่แล้วไม่ใช่สิ่งที่คุณต้องการ การเชื่อมแบบ 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

โปรดทราบว่าสคริปต์ผลลัพธ์ข้างต้นสามารถเขียนได้ดังต่อไปนี้เพื่อให้ได้ผลลัพธ์เดียวกัน

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 Outer JOIN จะส่งคืนเรคอร์ดทั้งหมดที่ตรงกันจากทั้งสองตาราง

สามารถตรวจจับบันทึกที่ไม่ตรงกันในตารางที่เข้าร่วมได้ มันกลับมา NULL ค่าสำหรับบันทึกของตารางที่รวมเข้าด้วยกันหากไม่พบรายการที่ตรงกัน

ฟังดูสับสนใช่ไหม? มาดูตัวอย่างกันดีกว่า –

LEFT JOIN

สมมติว่าตอนนี้คุณต้องการเรียกชื่อภาพยนตร์ทั้งหมดพร้อมกับชื่อสมาชิกที่เช่าภาพยนตร์เหล่านั้น เห็นได้ชัดว่าภาพยนตร์บางเรื่องไม่ได้ถูกเช่าโดยใครเลย เราก็สามารถใช้ LEFT JOIN สำหรับจุดประสงค์.

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 โปรแกรม Workbench จะแสดงผลลัพธ์ดังที่แสดงด้านล่าง คุณจะเห็นได้ว่าสำหรับภาพยนตร์ที่ไม่ได้เช่า ฟิลด์ชื่อสมาชิกจะมีค่าเป็น 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 Workbench ให้ผลลัพธ์ต่อไปนี้

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 อยู่ MySQL เสนอทางเลือกที่สั้นกว่าเมื่อคอลัมน์ที่ตรงกันมีชื่อเดียวกัน

ในตัวอย่างแบบสอบถาม JOIN ข้างต้น เราได้ใช้ส่วนคำสั่ง ON เพื่อจับคู่บันทึกระหว่างตาราง

USING clause สามารถใช้เพื่อจุดประสงค์เดียวกันได้ ความแตกต่างกับ ใช้ ใช่ไหม ต้องมีชื่อเหมือนกันสำหรับคอลัมน์ที่ตรงกันในทั้งสองตาราง

ในตาราง "ภาพยนตร์" จนถึงตอนนี้ เราใช้คีย์หลักชื่อ "id" เราเรียกสิ่งเดียวกันนี้ในตาราง "สมาชิก" ด้วยชื่อ "movie_id"

มาเปลี่ยนชื่อฟิลด์ "id" ของตาราง "ภาพยนตร์" ให้เป็นชื่อ "movie_id" เราทำเช่นนี้เพื่อให้ชื่อฟิลด์ที่ตรงกันตรงกัน

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

ต่อไปลองใช้ USING กับตัวอย่าง LEFT JOIN ด้านบน

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

นอกเหนือจากการใช้ ON และ การใช้ JOIN คุณสามารถใช้อื่น ๆ อีกมากมาย MySQL ข้อที่ชอบ จัดกลุ่มตาม, ที่ไหน และแม้กระทั่งฟังก์ชั่นเช่น SUM, AVGฯลฯ

คำถามที่พบบ่อย