MySQL เข้าร่วมบทช่วยสอน: INNER, OUTER, LEFT, RIGHT, CROSS

โดย: มาร์คัสอัลเลน มาร์คัสอัลเลน
Hours วันที่อัพเดท

JOINS คืออะไร?

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

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

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

id ชื่อจริง นามสกุล movie_id
1 อาดัม Smith 1
2 ราวี Kumar 2
3 ซูซาน เดวิดสัน 5
4 สัตว์ตัวเมีย Adrianna 8
5 Lee สาบ 10
id ชื่อเรื่อง หมวดหมู่
1 ASSASSIN'S CREED: ถ่าน ภาพเคลื่อนไหว
2 รีล สตีล(2012) ภาพเคลื่อนไหว
3 อัลวินและ Chipmunks ภาพเคลื่อนไหว
4 การผจญภัยของทิน ทิน ภาพเคลื่อนไหว
5 ปลอดภัย (2012) การกระทำ
6 เซฟเฮาส์(2012) การกระทำ
7 จีไอเอ 18 +
8 กำหนดเวลา 2009 18 +
9 ภาพสกปรก 18 +
10 มาร์ลีย์และฉัน โรแมนติก

ประเภทของการรวม

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

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

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

ประโยค “เปิด” และ “การใช้”

ในตัวอย่างแบบสอบถาม 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ฯลฯ

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

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

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

สรุป

  • JOINS ช่วยให้เราสามารถรวมข้อมูลจากตารางมากกว่าหนึ่งตารางให้เป็นชุดผลลัพธ์เดียว
  • JOINS มีประสิทธิภาพที่ดีกว่าเมื่อเทียบกับแบบสอบถามย่อย
  • INNER JOINS ส่งคืนแถวที่ตรงตามเกณฑ์ที่กำหนดเท่านั้น
  • OUTER JOINS ยังสามารถส่งคืนแถวที่ไม่พบรายการที่ตรงกันได้ แถวที่ไม่ตรงกันจะถูกส่งกลับพร้อมกับคีย์เวิร์ด NULL
  • ประเภท JOIN ที่สำคัญ ได้แก่ Inner, Left Outer, Right Outer, Cross JOINS เป็นต้น
  • คำสั่งที่ใช้บ่อยในการดำเนินการ JOIN คือ “ON” คำสั่ง “USING” กำหนดให้คอลัมน์ที่ตรงกันต้องมีชื่อเดียวกัน
  • JOINS ยังสามารถนำมาใช้ในส่วนคำสั่งอื่นๆ เช่น GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS เป็นต้น