ทำความรู้จัก Subquery การเขียนคำสั่งซ้อนในคำสั่ง
เวลาเราเขียน Query (คำสั่งถามข้อมูลจากฐานข้อมูล) บางครั้งเราไม่ได้ต้องการข้อมูลแค่จากตารางเดียว แต่เราต้องการผลลัพธ์จากอีกคำสั่งหนึ่งมาเป็นเงื่อนไขในการค้นหา นี่แหละคือจุดที่ Subquery (การเขียนคำสั่งสอบถามซ้อนไว้ในอีกคำสั่งหนึ่ง) เข้ามามีบทบาทสำคัญ ลองจินตนาการว่าคุณกำลังสั่งอาหารในแอปเดลิเวอรี คุณต้องรู้ก่อนว่า "ร้านไหนมีเมนูที่คุณอยากกิน" แล้วค่อยสั่งอาหารจากร้านนั้น ถ้าให้เขียนเป็นโปรแกรม เราต้องหาชื่อร้านก่อน แล้วค่อยใช้ชื่อร้านนั้นไปค้นหาเมนูอีกที Subquery ช่วยให้เราทำเรื่องนี้ได้ในคำสั่งเดียว ไม่ต้องเขียนแยกหลายขั้นตอน มือใหม่มักจะสับสนว่าเมื่อไหร่ควรใช้ Subquery ให้จำไว้ว่า ถ้าคุณต้องใช้ผลลัพธ์จากตารางหนึ่งมาเป็นตัวกรองให้ตารางหลัก นั่นคือสัญญาณว่าต้องใช้มันแล้ว การเขียนแบบนี้ทำให้โค้ดของเราดูสะอาดและจัดการข้อมูลที่มีความสัมพันธ์ซับซ้อนได้ง่ายขึ้น
-- ค้นหาพนักงานที่มีเงินเดือนมากกว่าเงินเดือนเฉลี่ยของบริษัท
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
อธิบายโค้ด:
SELECT AVG(Salary) FROM Employeesคือส่วนที่ซ้อนอยู่ข้างใน เพื่อหาค่าเฉลี่ยเงินเดือนของทุกคนก่อนWHERE Salary > (...)คือคำสั่งหลักที่เอาค่าที่ได้จากข้างในมาเปรียบเทียบกับเงินเดือนของพนักงานแต่ละคน
ผลลัพธ์ที่ได้: รายชื่อพนักงานและเงินเดือนของคนที่ได้รับเงินมากกว่าค่าเฉลี่ยของบริษัททุกคนออกมาเป็นตาราง
ข้อจำกัดของ Subquery และจุดที่ต้องระวัง
แม้ Subquery จะดูมีประโยชน์มาก แต่ถ้าเราใช้ไม่ระวัง มันอาจทำให้โปรแกรมทำงานช้าลงอย่างมาก โดยเฉพาะเมื่อข้อมูลในฐานข้อมูลมีจำนวนมหาศาล เพราะฐานข้อมูลต้องประมวลผลคำสั่งข้างในซ้ำๆ สำหรับทุกแถวของคำสั่งข้างนอก จุดที่มือใหม่พลาดบ่อยคือการเขียน Subquery ที่คืนค่าออกมามากกว่าหนึ่งแถว แต่เอาไปใช้กับตัวดำเนินการเปรียบเทียบอย่าง = หรือ > ซึ่งจะทำให้เกิดข้อผิดพลาดทันที เพราะฐานข้อมูลไม่รู้จะเอาค่าไหนไปเปรียบเทียบกับแถวนั้นๆ ทางแก้คือต้องใช้ตัวดำเนินการที่รองรับหลายค่า เช่น IN (คำสั่งตรวจสอบว่าค่าอยู่ในกลุ่มที่กำหนดไหม) แทนการใช้เครื่องหมายเท่ากับธรรมดา และพยายามหลีกเลี่ยงการเขียน Subquery ซ้อนกันหลายชั้นเกินไป เพราะจะทำให้ไล่โค้ดลำบากและแก้ไขบั๊ก (ข้อผิดพลาดในโปรแกรม) ได้ยากในอนาคต
รู้จัก Table-Valued Function ตัวช่วยสร้างตารางจำลอง
ก่อนจะไปถึง CROSS APPLY เราต้องรู้จัก Table-Valued Function (ฟังก์ชันที่ทำงานแล้วคืนค่าออกมาเป็นรูปแบบตาราง) กันก่อน ปกติฟังก์ชันทั่วไปจะคืนค่าออกมาแค่ค่าเดียว เช่น ตัวเลขหรือข้อความ แต่ฟังก์ชันประเภทนี้จะให้ผลลัพธ์เป็นชุดข้อมูลเหมือนตารางจริง ลองคิดว่ามันคือ "แม่พิมพ์ทำขนม" ที่เราใส่ส่วนผสม (ข้อมูล) ลงไป แล้วมันจะผลิตขนมออกมาเป็นชุด (ตาราง) ให้เราเสมอ ฟังก์ชันนี้มีประโยชน์มากเวลาเราต้องการคำนวณข้อมูลที่ซับซ้อนแยกต่างหาก แล้วค่อยเอาผลลัพธ์นั้นมาเชื่อมกับข้อมูลหลักของเรา ถ้าไม่มี Table-Valued Function เราอาจต้องเขียนโค้ดชุดเดิมซ้ำๆ หลายที่ แต่ถ้าเราสร้างมันขึ้นมาครั้งเดียว เราก็เรียกใช้ได้ตลอด ช่วยลดการเขียนโค้ดที่ซ้ำซ้อนและทำให้การบำรุงรักษาโค้ดในระยะยาวทำได้ง่ายขึ้นมาก
-- สร้างฟังก์ชันหาพนักงานในแผนกที่ระบุ
CREATE FUNCTION GetEmployeesByDept (@DeptID INT)
RETURNS TABLE
AS
RETURN (
SELECT Name, Salary FROM Employees WHERE DepartmentID = @DeptID
);
อธิบายโค้ด:
CREATE FUNCTIONคือคำสั่งสร้างฟังก์ชัน@DeptID INTคือตัวแปรที่รับค่ารหัสแผนกเข้ามาRETURNS TABLEบอกว่าฟังก์ชันนี้จะส่งผลลัพธ์ออกมาเป็นตาราง
ผลลัพธ์ที่ได้: เราจะได้ฟังก์ชันชื่อ GetEmployeesByDept ที่พร้อมใช้งานเหมือนตารางตัวหนึ่ง
ความมหัศจรรย์ของ CROSS APPLY
CROSS APPLY คือสิ่งที่ถูกสร้างมาเพื่อ MS-SQL โดยเฉพาะ มันทำหน้าที่เหมือนการนำตารางหลักไปเชื่อมกับ Table-Valued Function โดยที่ฟังก์ชันนั้นสามารถดึงค่าจากตารางหลักมาใช้เป็นพารามิเตอร์ (ตัวแปรขาเข้า) ได้แบบแถวต่อแถว เปรียบเทียบง่ายๆ เหมือนคุณมีรายชื่อพนักงาน แล้วคุณมีเครื่องมือวิเคราะห์ (ฟังก์ชัน) ที่ต้องใช้ "ชื่อพนักงาน" เป็นตัวตั้งต้น CROSS APPLY จะหยิบพนักงานคนแรกไปใส่เครื่องมือ แล้วเอาผลลัพธ์มาวางคู่กัน ทำแบบนี้ไปเรื่อยๆ จนครบทุกคน ถ้าใช้ JOIN (คำสั่งเชื่อมตาราง) ปกติ เราไม่สามารถส่งค่าจากตารางหลักเข้าไปในฟังก์ชันได้โดยตรง แต่ CROSS APPLY ออกแบบมาเพื่อแก้ปัญหานี้โดยเฉพาะ ทำให้การเชื่อมข้อมูลที่ต้องการการประมวลผลพิเศษกลายเป็นเรื่องง่าย
-- เชื่อมข้อมูลพนักงานกับฟังก์ชันหาเงินเดือนย้อนหลัง 3 เดือน
SELECT E.Name, D.Salary
FROM Employees AS E
CROSS APPLY GetLastThreeMonthsSalary(E.EmployeeID) AS D;
อธิบายโค้ด:
CROSS APPLYจะวิ่งไปหาพนักงานทีละคนGetLastThreeMonthsSalary(E.EmployeeID)จะส่งรหัสพนักงานคนนั้นเข้าฟังก์ชันAS Dคือการตั้งชื่อเล่นให้ผลลัพธ์ที่ได้จากฟังก์ชัน
ผลลัพธ์ที่ได้: ตารางรายชื่อพนักงานที่แสดงเงินเดือนย้อนหลัง 3 เดือนของแต่ละคนเรียงต่อกัน
ความต่างระหว่าง CROSS APPLY และ OUTER APPLY
มือใหม่มักสงสัยว่าแล้ว OUTER APPLY ต่างจาก CROSS APPLY อย่างไร คำตอบสั้นๆ คือเรื่องของข้อมูลที่ "ไม่มีผลลัพธ์" ถ้าฟังก์ชันของคุณไม่คืนค่าอะไรออกมาเลย CROSS APPLY จะตัดแถวนั้นทิ้งไปจากผลลัพธ์ทันที แต่ถ้าคุณใช้ OUTER APPLY มันจะยังคงแสดงแถวของตารางหลักเอาไว้ แม้ว่าฟังก์ชันจะไม่มีข้อมูลส่งกลับมาก็ตาม โดยช่องที่ควรจะเป็นข้อมูลจากฟังก์ชันจะกลายเป็นค่าว่าง (NULL) แทน ซึ่งเหมือนกับการใช้ LEFT JOIN (การเชื่อมตารางแบบเอาข้อมูลฝั่งซ้ายเป็นหลัก) นั่นเอง การเลือกว่าจะใช้ตัวไหน ขึ้นอยู่กับว่าคุณต้องการข้อมูลนั้นหรือไม่ ถ้าคุณต้องการแค่รายชื่อพนักงานที่มีเงินเดือนเท่านั้นให้ใช้ CROSS APPLY แต่ถ้าต้องการรายชื่อพนักงานทุกคนโดยไม่สนว่าจะมีข้อมูลเงินเดือนหรือไม่ ให้เลือกใช้ OUTER APPLY
-- ใช้ OUTER APPLY เพื่อให้พนักงานที่ไม่มีประวัติเงินเดือนยังปรากฏอยู่
SELECT E.Name, D.Salary
FROM Employees AS E
OUTER APPLY GetLastThreeMonthsSalary(E.EmployeeID) AS D;
อธิบายโค้ด:
OUTER APPLYทำงานเหมือนCROSS APPLYแต่ไม่ตัดแถวที่ฟังก์ชันคืนค่าว่าง- ถ้าไม่มีข้อมูลเงินเดือน
D.Salaryจะแสดงเป็นค่าว่างแต่ชื่อพนักงานยังอยู่
ผลลัพธ์ที่ได้: รายชื่อพนักงานทุกคนในบริษัท แม้บางคนจะไม่มีประวัติเงินเดือนย้อนหลังก็ตาม
สรุปการประยุกต์ใช้งานจริง
การเข้าใจ Subquery และกลุ่มคำสั่ง APPLY จะช่วยให้คุณจัดการข้อมูลที่ซับซ้อนในฐานข้อมูลได้อย่างมืออาชีพ ไม่ว่าจะเป็นการดึงข้อมูลสถิติรายบุคคล หรือการเชื่อมต่อกับฟังก์ชันที่ต้องคำนวณค่าเฉพาะเจาะจง ลองนึกภาพโปรเจกต์ทำระบบรายงานเงินเดือน หากคุณต้องดึงข้อมูลพนักงานพร้อมกับยอดรวมโบนัสที่ต้องคำนวณผ่านสูตรพิเศษ ฟังก์ชัน Table-Valued ร่วมกับ OUTER APPLY จะเป็นเครื่องมือที่ทรงพลังที่สุดในการดึงข้อมูลนี้ออกมาในคราวเดียวโดยไม่ต้องเขียนโค้ดซ้ำซ้อน จำไว้ว่าพื้นฐานที่ดีคือการฝึกฝน เริ่มจากการเขียน Subquery ง่ายๆ ก่อน แล้วค่อยขยับมาใช้ APPLY เมื่อต้องเจอกับงานที่ยากขึ้น หมั่นทดลองรันโค้ดบ่อยๆ แล้วคุณจะเริ่มเห็นความสวยงามของการเชื่อมข้อมูลที่ลื่นไหลแบบนี้เองครับ
