MySQL แบบสอบถามย่อยพร้อมตัวอย่าง

SubQuery ใน SQL คืออะไร?

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

แบบสอบถามภายในเรียกว่า แบบสอบถามภายใน หรือแบบสอบถามแบบซ้อนกัน และคำสั่งที่บรรจุแบบสอบถามนั้นเรียกว่า การสอบถามภายนอกเรามาดูไวยากรณ์ของซับเควรีกัน

แผนภาพด้านบนแสดงโครงสร้างทั่วไปของคำสั่ง: คำสั่ง SELECT ภายนอกระบุคอลัมน์ที่คุณต้องการดู และคำสั่ง SELECT ภายในที่อยู่ในวงเล็บระบุค่าหรือรายการค่าที่ใช้ในการเปรียบเทียบในส่วน WHERE

เหตุใดจึงต้องใช้ซับเควรี?

ก่อนที่จะไปดูประเภทต่างๆ เราควรทำความเข้าใจก่อนว่าเมื่อใดที่ซับเควรีเหมาะสมที่จะใช้ในคำสั่ง SQL

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

การค้นหาย่อยอยู่ที่tracมีเหตุผลเชิงปฏิบัติสามประการที่ทำให้ควรเลือกใช้:

• การอ่าน: แต่ละส่วนของตรรกะจะอยู่ในวงเล็บแยกกัน ทำให้ประโยคนั้นอ่านได้เหมือนเป็นลำดับของคำถามเล็กๆ แทนที่จะเป็นนิพจน์ที่ซับซ้อนเพียงอย่างเดียว
• การแยก: สามารถเรียกใช้คิวรีภายในได้โดยอิสระเพื่อยืนยันว่าได้ค่าที่คาดหวังไว้ ซึ่งทำให้การทดสอบและการแก้ไขข้อผิดพลาดง่ายขึ้นมาก
• ความยืดหยุ่น: รูปแบบเดียวกันนี้ใช้ได้กับเงื่อนไข WHERE, HAVING, SELECT และ FROM รวมถึงภายในคำสั่ง INSERT, UPDATE และ DELETE ด้วย

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

ประเภทของซับเควรีใน MySQL

MySQL รองรับการใช้ซับเควรี 3 ประเภท โดยประเภทจะขึ้นอยู่กับรูปแบบของผลลัพธ์ที่เควรีภายในส่งคืน แต่ละประเภทจะอธิบายไว้ด้านล่างพร้อมตัวอย่างการใช้งานกับฐานข้อมูล myflixdb

1) แบบสอบถามย่อยแบบสเกลาร์

A แบบสอบถามย่อยแบบสเกลาร์ คำสั่งนี้จะส่งคืนแถวเดียวและคอลัมน์เดียว ซึ่งหมายความว่ามันจะส่งคืนค่าเดียว เนื่องจากผลลัพธ์เป็นค่าเดียว จึงสามารถใช้ได้ทุกที่ที่อนุญาตให้ใช้ค่าคงที่ได้ กลับมาที่ปัญหา MyFlix ข้างต้น คุณสามารถใช้คำสั่งแบบนี้ได้:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

ผลลัพธ์ที่ได้คือ:

มาดูกันว่าคำสั่งนี้ทำงานอย่างไร

ดังที่แผนภาพการดำเนินการแสดงไว้ MySQL วิ่งครั้งแรก SELECT MIN(category_id) FROM moviesรับค่าเพียงค่าเดียว จากนั้นจึงเรียกใช้คำสั่งค้นหาภายนอกโดยใช้ค่านั้น เนื่องจากส่งค่ากลับมาเพียงค่าเดียว ตัวดำเนินการที่อนุญาตจึงเป็นชุดการเปรียบเทียบมาตรฐาน: =, <> (หรือ !=), >, >=, <และ <=.

💡 เคล็ดลับ: หากวางซับเควรีไว้หลังจากนั้น = ส่งคืนมากกว่าหนึ่งแถว MySQL ทำให้เกิดข้อผิดพลาด 1242 แบบสอบถามย่อยส่งคืนข้อมูลมากกว่า 1 แถวเปลี่ยนตัวดำเนินการเป็น INหรือกระชับเงื่อนไข WHERE ภายในให้แน่นขึ้น

2) การค้นหาย่อยแถว

A แบบสอบถามย่อยแถว นอกจากนี้ยังส่งคืนแถวเดียว แต่แถวนั้นอาจมีมากกว่าหนึ่งคอลัมน์ ดังนั้นแบบสอบถามภายนอกจึงเปรียบเทียบค่าในแถวกับตัวสร้างแถวแทนที่จะเปรียบเทียบกับค่าเดียว

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

ตัวดำเนินการที่อนุญาตให้ใช้ได้คือตัวดำเนินการเปรียบเทียบแบบเดียวกันกับที่ระบุไว้ข้างต้น โดยนำไปใช้กับทั้งแถวพร้อมกัน

3) แบบสอบถามย่อยของตาราง

A ตารางย่อย คำสั่งค้นหาภายนอกจะส่งคืนหลายแถว และมักจะมีหลายคอลัมน์ ดังนั้นคำสั่งค้นหาภายนอกจึงต้องใช้ตัวดำเนินการเซต เช่น IN, NOT IN, ANY, ALLหรือ EXISTS.

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

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

มาดูกันว่าคำสั่งนี้ทำงานอย่างไร

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

การซ้อนแบบสอบถามย่อยหลายระดับลึก

จนถึงตอนนี้คุณได้เห็นไปแล้วสองระดับ ซับเควรีอาจประกอบด้วยซับเควรีอื่นอีก ซึ่งจะทำให้เกิดคำสั่งซ้อนกันสามชั้น

สมมติว่าฝ่ายบริหารต้องการให้รางวัลแก่สมาชิกที่จ่ายเงินสูงสุด เราสามารถเรียกใช้คำสั่งค้นหาแบบนี้ได้:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

คำสั่งค้นหาภายในสุดจะค้นหาจำนวนเงินที่มากที่สุด คำสั่งค้นหาตรงกลางจะแปลงจำนวนเงินนั้นเป็นหมายเลขสมาชิก และคำสั่งค้นหาภายนอกสุดจะแปลงหมายเลขสมาชิกเป็นชื่อ คำสั่งค้นหาข้างต้นให้ผลลัพธ์ดังต่อไปนี้:

วิธีการใช้ซับเควรีร่วมกับคำสั่ง INSERT, UPDATE และ DELETE

การใช้ซับเควรีไม่ได้จำกัดอยู่แค่ในคำสั่ง SELECT เท่านั้น รูปแบบวงเล็บเดียวกันนี้สามารถใช้ได้ภายในคำสั่งแก้ไขข้อมูล ซึ่งช่วยให้สามารถเปลี่ยนแปลงข้อมูลทั้งแถวได้ในครั้งเดียวโดยไม่ต้องสร้างตารางชั่วคราว

แทรกข้อมูลโดยใช้ซับเควรี ซับเควรีสามารถให้ข้อมูลแถวที่จะถูกแทรก ซึ่งเป็นการคัดลอกข้อมูลจากตารางหนึ่งไปยังอีกตารางหนึ่ง รายชื่อคอลัมน์ของคำสั่ง SELECT ต้องตรงกับรายชื่อคอลัมน์ของคำสั่ง INSERT

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

อัปเดตด้วยซับเควรี ในที่นี้ คำสั่งค้นหาภายในจะเป็นตัวกำหนดว่าแถวใดบ้างที่จะถูกเรียกใช้ ตัวอย่างด้านล่างจะทำเครื่องหมายสมาชิกทุกคนที่ยังค้างชำระค่าเช่าอยู่

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

ลบข้อมูลโดยใช้ซับเควรี หลักการเดียวกันนี้ใช้ในการลบแถวที่ตรงตามเงื่อนไขที่กำหนดไว้ในตารางที่สอง

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️คำเตือน: MySQL ไม่อนุญาตให้คำสั่งแก้ไขตารางและเลือกข้อมูลจากตารางเดียวกันภายในซับควอรีในส่วน FROM หากเกิดข้อผิดพลาด 1093 ให้ห่อซับควอรีภายในด้วยตารางที่ได้มา เช่น SELECT * FROM (SELECT ...) AS tเพื่อให้ MySQL แสดงผลลัพธ์ก่อนที่จะมีการเปลี่ยนแปลง นอกจากนี้ ควรเรียกใช้คำสั่ง SELECT ภายในแยกต่างหากก่อน และตรวจสอบจำนวนแถวก่อนที่จะเรียกใช้คำสั่ง SELECT อีกครั้ง อัพเดท หรือ ลบ ในการผลิต

ซับเควรี vs การเชื่อมตาราง

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

เมื่อเปรียบเทียบกับการเชื่อมตาราง (joins) แล้ว ซับควอรี (sub-queries) นั้นใช้งานง่ายและอ่านง่ายกว่า ไม่ซับซ้อนเท่ากับการเชื่อมตาราง (joins) ร่วมและด้วยเหตุนี้จึงมักถูกนำไปใช้โดย ผู้เริ่มต้น SQL.

แต่การใช้ซับเควรีมีปัญหาเรื่องประสิทธิภาพ การใช้ join แทนซับเควรีบางครั้งอาจเพิ่มประสิทธิภาพได้มากถึง 500 เท่า เพราะตัวปรับแต่งประสิทธิภาพสามารถประมวลผล join ได้ในครั้งเดียว แทนที่จะประเมินคำสั่งภายในซ้ำๆ

เมื่อเลือกตัวเลือกแล้ว ขอแนะนำให้ใช้ JOIN เหนือแบบสอบถามย่อย ควรใช้ซับเควรีเป็นทางเลือกสำรองเท่านั้น ในกรณีที่คุณไม่สามารถใช้การดำเนินการ JOIN เพื่อให้ได้ผลลัพธ์ข้างต้นได้

นอกจากนี้ ซับเควรียังสามารถแยกย่อยออกเป็นส่วนประกอบเชิงตรรกะได้ง่าย ซึ่งมีประโยชน์มากเมื่อ การทดสอบ และแก้ไขคำถาม

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