ตัวดำเนินการเซตใน SQL: คู่มือฉบับสมบูรณ์

ปฏิบัติการแบบเซตใน SQL คืออะไร?

ปฏิบัติการแบบเซตใน SQL คือเทคนิคสำหรับผสานหรือเปรียบเทียบผลลัพธ์จากคำสั่ง SELECT ตั้งแต่สองคำสั่งขึ้นไป ทำงานเสมือนปฏิบัติการแบบเซตในคณิตศาสตร์ ช่วยให้ค้นหา union, intersection หรือความแตกต่างระหว่างแถวที่คิวรีของเราคืนค่า ช่วยให้การวิเคราะห์ข้อมูลจากหลายแหล่งหรือหลายมุมมองทำได้สะดวก

นี่คือภาพรวมของปฏิบัติการหลัก:

• UNION: ผสานแถวที่ไม่ซ้ำกันทั้งหมดจากคำสั่ง SELECT ตั้งแต่สองคำสั่งขึ้นไป โดยตัดแถวซ้ำทิ้ง

• UNION ALL: ผสานแถวทั้งหมดจากคำสั่ง SELECT ตั้งแต่สองคำสั่งขึ้นไป โดยคงแถวซ้ำไว้

• INTERSECT: คืนค่าเฉพาะแถวที่ปรากฏในทั้งสองคำสั่ง SELECT

• EXCEPT: คืนค่าแถวจากคำสั่ง SELECT แรกที่ไม่มีอยู่ในคำสั่งที่สอง

การเปรียบเทียบกับปฏิบัติการในพีชคณิตสัมพันธ์

พีชคณิตสัมพันธ์เป็นกรอบทฤษฎีที่เป็นรากฐานในการทำความเข้าใจคิวรีฐานข้อมูล มีปฏิบัติการเชิงนามธรรม เช่น projection, selection และ join อิงหลักคณิตศาสตร์และเป็นอิสระจากระบบฐานข้อมูลใดๆ ให้มองว่าเป็นตรรกะ "เบื้องหลัง" ที่ขับเคลื่อนการทำงานกับฐานข้อมูลของเรา

ตัวดำเนินการเซตใน SQL คือการนำแนวคิดเหล่านี้มาปฏิบัติจริงภายในสภาพแวดล้อมฐานข้อมูล ช่วยให้ทำปฏิบัติการแบบเซต เช่น union, intersection และ difference ได้โดยตรงกับชุดผลลัพธ์ของคิวรี SQL

แม้พีชคณิตสัมพันธ์จะให้รากฐานเชิงรูปแบบสำหรับปฏิบัติการฐานข้อมูล แต่ตัวดำเนินการเซตใน SQL ก็ให้ส่วนติดต่อที่เป็นมาตรฐานและใช้งานง่ายสำหรับงานจัดการข้อมูล

การเข้าใจความสัมพันธ์ระหว่างตัวดำเนินการเซตกับปฏิบัติการในพีชคณิตสัมพันธ์ช่วยให้เห็นรากฐานทางทฤษฎีของ SQL และช่วยให้เขียนคิวรีด้วยแบบจำลองความคิดที่ชัดเจนขึ้นว่าเบื้องหลังเกิดอะไรขึ้น

ประเภทของตัวดำเนินการเซตใน SQL

ตัวดำเนินการเซตหลักใน SQL มีสามตัว:

• UNION

• INTERSECT

• EXCEPT (หรือ MINUS ในบางไดอาเล็กต์)

ตัวดำเนินการเหล่านี้สอดคล้องเชิงคณิตศาสตร์กับแนวคิด union, intersection และ set difference

กฎการใช้ตัวดำเนินการเซต

ก่อนผสานคิวรีด้วยตัวดำเนินการเซต ต้องเป็นไปตามข้อกำหนดสี่ข้อ หากผิดข้อใด คิวรีจะเกิดข้อผิดพลาด

• จำนวนคอลัมน์เท่ากัน: แต่ละ SELECT ต้องคืนค่าจำนวนคอลัมน์เท่ากัน หากคิวรีหนึ่งได้สามคอลัมน์ แต่อีกคิวรีได้สอง ฐานข้อมูลจะขว้างข้อผิดพลาด

• ชนิดข้อมูลเข้ากันได้: คอลัมน์ที่ตรงกันต้องมีชนิดข้อมูลสอดคล้องกัน ไม่สามารถผสานคอลัมน์ชื่อที่เป็น VARCHAR เข้ากับคอลัมน์รหัสที่เป็น INTEGER ในตำแหน่งเดียวกันได้

• ลำดับคอลัมน์เหมือนกัน: SQL จับคู่คอลัมน์ตามตำแหน่ง ไม่ใช่ชื่่อ คอลัมน์แรกของคิวรีแรกจะจับคู่กับคอลัมน์แรกของคิวรีที่สอง ไม่ว่าคอลัมน์เหล่านั้นจะชื่ออะไร

• ORDER BY ใช้ได้เฉพาะท้ายสุด: ORDER BY ต้องปรากฏเพียงครั้งเดียว ที่ท้ายสุดของคิวรีที่ผสานกัน ห้ามใช้ภายในคำสั่ง SELECT แต่ละตัว

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

วิธีใช้ตัวดำเนินการ UNION ใน SQL

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

เช่น สมมติว่ามีสองตารางคือ employees และ contractors แต่ละตารางมีคอลัมน์คล้ายกัน เช่น contractors, department และ salary เพื่อการเรียนรู้ มาดูตารางตัวอย่างสองตารางนี้:

employees:

contractors:

เราสามารถผสานผลลัพธ์จากทั้งสองตารางด้วยคำสั่งต่อไปนี้:

-- Using UNION to combine all employees and contractors
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

คิวรีนี้เลือกคอลัมน์ name, department และ salary จากทั้งตาราง employees และ contractors แล้วผสานเป็นชุดผลลัพธ์เดียว ตัวดำเนินการ UNION จะลบแถวซ้ำโดยอัตโนมัติจากผลลัพธ์สุดท้าย

สังเกตว่า Carol ซึ่งปรากฏในทั้งสองตาราง จะแสดงเพียงครั้งเดียวในผลลัพธ์ หากต้องการเก็บทั้งสองรายการของ Carol (ซึ่งมีเงินเดือนต่างกัน) ให้ใช้ UNION ALL

UNION vs UNION ALL

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

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

วิธีใช้ตัวดำเนินการ INTERSECT ใน SQL

ตัวดำเนินการ INTERSECT จะคืนค่าเฉพาะแถวที่ปรากฏอยู่ในทั้งสองชุดผลลัพธ์ ให้นึกว่าเป็นการหาผู้คนที่อยู่ในทั้งสองกลุ่ม

มาลองใช้ INTERSECT กับตารางด้านบน เพื่อให้ง่าย สมมติว่าเราคิวรีเฉพาะคอลัมน์ name และ department:

-- Using INTERSECT to find common employees
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

คิวรีนี้เลือกคอลัมน์ name และ department จากทั้งตาราง employees และ contractors และจะคืนค่าเฉพาะแถวที่มีอยู่ในทั้งสองตาราง โดยพิจารณาจากทุกคอลัมน์ที่เลือก

ตัวดำเนินการ INTERSECT จัดการค่า NULL ตามกฎการเปรียบเทียบมาตรฐาน โดยถือว่าค่า NULL เท่ากันเมื่อเปรียบเทียบคอลัมน์ที่สอดคล้องกัน และให้ผลลัพธ์เป็นเซตว่างเมื่อจัดการกับชุดผลลัพธ์ว่าง

กล่าวคือ หากมีค่า NULL ปรากฏในชุดผลลัพธ์หนึ่ง และคอลัมน์ที่สอดคล้องกันในอีกชุดมีค่าไม่เป็น NULL แถวเหล่านั้นจะไม่ถือว่าเท่ากัน และจะไม่ถูกนำมารวมในผลลัพธ์ของ intersection

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

วิธีใช้ตัวดำเนินการ UNION ใน SQL

ตัวดำเนินการ EXCEPT จะคืนค่าแถวจากชุดผลลัพธ์แรกที่ไม่มีอยู่ในชุดที่สอง

ใน Oracle Database เขียน EXCEPT เป็น MINUS ส่วนไดอาเล็กต์หลักอื่นๆ (PostgreSQL, SQL Server, MySQL 8.0.31+, SQLite) ใช้ EXCEPT

ตัวอย่างเช่น ลองรันคิวรีต่อไปนี้:

-- Using EXCEPT to find employees who are not contractors
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

คอลัมน์ name, department และ salary ถูกเลือกจากตาราง employees และจะคืนค่าเฉพาะแถวที่ไม่มีอยู่ในตาราง contractors

ตัวดำเนินการ EXCEPT ปฏิบัติตามกฎการเปรียบเทียบมาตรฐานในการจัดการค่า NULL พฤติกรรมเมื่อเจอชุดผลลัพธ์ว่างคือ หากชุดผลลัพธ์แรกว่าง ผลลัพธ์รวมก็จะว่าง หรือหากชุดผลลัพธ์ที่สองว่าง ก็จะรวมทุกแถวจากชุดผลลัพธ์แรก

ตัวดำเนินการเซตใน SQL: ประสิทธิภาพและการเพิ่มประสิทธิภาพ

ผลกระทบของตัวดำเนินการเซตต่อประสิทธิภาพคิวรีใน SQL อาจแตกต่างกันไปตามปัจจัยอย่างขนาดชุดข้อมูล ความซับซ้อนของคิวรี และระบบจัดการฐานข้อมูล (DBMS) ที่ใช้

มาดูปัจจัยสำคัญและกลยุทธ์การเพิ่มประสิทธิภาพกัน

ปริมาณข้อมูลและความซับซ้อนของคิวรี

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

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

การทำดัชนีและเทคนิคการเพิ่มประสิทธิภาพ

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

เพื่อปรับปรุงประสิทธิภาพของคิวรีที่เกี่ยวข้องกับตัวดำเนินการเซต ผู้ดูแลฐานข้อมูลและนักพัฒนาสามารถใช้เทคนิคอย่างการเขียนคิวรีใหม่ การวิเคราะห์ execution plan และการปรับสคีมา เทคนิคอย่างการแคชคิวรีและ materialized view ก็สามารถใช้คำนวณล่วงหน้าและเก็บผลลัพธ์ของคิวรีที่ซับซ้อนได้ เพื่อลดภาระการคำนวณจากปฏิบัติการแบบเซต

เอนจินฐานข้อมูลและทรัพยากรฮาร์ดแวร์

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

ความพร้อมของทรัพยากรฮาร์ดแวร์ เช่น CPU หน่วยความจำ และดิสก์ I/O ก็มีอิทธิพลต่อประสิทธิภาพของคิวรีที่เกี่ยวข้องกับตัวดำเนินการเซตเช่นกัน ทรัพยากรที่เพียงพอช่วยลดคอขวดและทำให้การรันคิวรีมีประสิทธิภาพ

ตัวดำเนินการเซตในทางปฏิบัติ

ตัวดำเนินการเซตไม่ใช่แค่เครื่องมือเชิงทฤษฎี แต่มีการใช้งานจริงที่ส่งผลต่อการตัดสินใจทางธุรกิจอย่างมาก มาดูตัวอย่างแบบย่อว่าบริษัทหนึ่งอาจใช้ตัวดำเนินการเซตเพื่อแบ่งกลุ่มลูกค้าสำหรับแคมเปญการตลาดเฉพาะกลุ่มได้อย่างไร

สถานการณ์

ลองนึกถึงบริษัทที่ขายทั้งออนไลน์และหน้าร้าน พวกเขามีชุดข้อมูลแยกกันสองชุด:

1. การซื้อออนไลน์: รหัสลูกค้า ประวัติการซื้อ ข้อมูลประชากร และที่ตั้งของผู้ซื้อออนไลน์
2. ธุรกรรมหน้าร้าน: ข้อมูลคล้ายกันสำหรับลูกค้าที่มาซื้อด้วยตนเอง

การใช้ตัวดำเนินการเซต

เพื่อให้เห็นภาพรวมของลูกค้าทั้งหมด บริษัทจะใช้ UNION ก่อนเพื่อผสานทั้งสองชุดข้อมูลให้เป็นตารางเดียว โดยตัดรายการซ้ำทิ้ง จะได้มุมมองรวมของฐานลูกค้าทั้งหมด

จากนั้นสามารถใช้ INTERSECT เพื่อระบุลูกค้าที่ช้อปทั้งออนไลน์และหน้าร้าน กลุ่มนี้มีคุณค่าเป็นพิเศษเพราะมีการมีส่วนร่วมกับแบรนด์หลายช่องทาง

เพื่อหาช่องทางโปรโมชันข้ามช่องทาง บริษัทอาจใช้ EXCEPT เช่น SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions จะหาลูกค้าที่ช้อปเฉพาะออนไลน์แต่ยังไม่เคยซื้อที่หน้าร้าน จากนั้นบริษัทสามารถทำโปรโมชันชวนให้ไปสาขาได้

ไกลกว่าการแบ่งกลุ่ม

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

ตัวดำเนินการเซต vs JOIN

ทั้งตัวดำเนินการเซตและ SQL JOIN ต่างก็ผสานข้อมูลจากหลายคิวรี แต่ทำงานต่างกัน แก่นคำถามคือ ต้องการผสานเป็นแถว หรือเป็นคอลัมน์

ข้อจำกัดและข้อพิจารณาของตัวดำเนินการเซต

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

ความเข้ากันได้ของชนิดข้อมูลและค่า NULL

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

ตัวดำเนินการเซตอาจปฏิบัติต่อค่า NULL ต่างกันไปตาม DBMS และตัวดำเนินการที่ใช้ เพื่อหลีกเลี่ยงความผิดพลาด นักพัฒนาควรเข้าใจว่าค่า NULL ถูกจัดการอย่างไร

ผลกระทบต่อประสิทธิภาพและแถวซ้ำ

ปฏิบัติการแบบเซตอาจส่งผลต่อประสิทธิภาพคิวรีอย่างมาก โดยเฉพาะเมื่อจัดการกับชุดข้อมูลขนาดใหญ่หรือซับซ้อน ปัจจัยอย่างการทำดัชนี การเพิ่มประสิทธิภาพคิวรี และทรัพยากรฮาร์ดแวร์ล้วนมีผล เทคนิคการเพิ่มประสิทธิภาพและการปรับจูนประสิทธิภาพเป็นสิ่งจำเป็นในการบรรเทาคอขวด

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

การเรียงลำดับผลลัพธ์และข้อจำกัดด้านหน่วยความจำ

ตัวดำเนินการเซตไม่รับประกันลำดับของผลลัพธ์ในเอาต์พุตสุดท้าย หากต้องการจัดเรียงผลลัพธ์ที่ผสาน ให้เติม ORDER BY clause ที่ท้ายสุดของคิวรีทั้งหมด หลังคำสั่ง SELECT สุดท้าย

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

ความซับซ้อน การดูแลรักษา และความเข้ากันได้ข้าม DBMS

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

ตัวดำเนินการเซตอาจมีความแตกต่างด้านไวยากรณ์และพฤติกรรมระหว่างระบบจัดการฐานข้อมูล (DBMS) ต่างๆ การรู้ความแตกต่างเหล่านี้เป็นสิ่งสำคัญเมื่อเขียนคิวรี SQL ให้ทำงานข้ามแพลตฟอร์ม

ข้อคิดส่งท้าย

ตัวดำเนินการเซตแก้ปัญหาเฉพาะ: การผสานหรือเปรียบเทียบชุดผลลัพธ์ โดยไม่ต้องรู้ล่วงหน้าว่าแถวใดซ้อนทับกัน

ครอบคลุม UNION, INTERSECT และ EXCEPT: ตัวดำเนินการสามตัวสำหรับผสาน เปรียบเทียบ และลบชุดผลลัพธ์

หากต้องการเรียนรู้เพิ่มเติม ลองดูคอร์ส Joining Data in SQL