การใช้ Set Operators
ตัวดำเนินการชุดใช้เพื่อรวมผลลัพธ์ของคำสั่ง SELECT สอง (หรือมากกว่า) ตัวดำเนินการ SET ที่มีอยู่ใน Oracle 11g คือ UNION, UNION ALL, INTERSECT และ MINUS
ตัวดำเนินการชุด UNION จะส่งคืนผลลัพธ์รวมของคำสั่ง SELECT ทั้งสองโดยพื้นฐานแล้วจะลบรายการที่ซ้ำกันออกจากผลลัพธ์เช่นจะมีเพียงแถวเดียวเท่านั้นที่จะแสดงรายการสำหรับผลลัพธ์ที่ซ้ำกันในการตอบโต้พฤติกรรมนี้ให้ใช้ตัวดำเนินการชุด UNION ALL ซึ่งจะเก็บข้อมูลซ้ำไว้ ผลลัพธ์สุดท้าย INTERSECT จะแสดงรายการเฉพาะเรกคอร์ดที่ใช้ร่วมกันกับทั้งแบบสอบถาม SELECT ตัวดำเนินการชุด MINUS จะลบผลลัพธ์ของแบบสอบถามที่สองออกจากผลลัพธ์หากพบในผลลัพธ์ของแบบสอบถามแรกด้วย การดำเนินการชุด INTERSECT และ MINUS ให้ผลลัพธ์ที่ไม่ซ้ำซ้อน
ตัวดำเนินการ SET ทั้งหมดมีระดับความสำคัญเท่ากันในระหว่างการดำเนินการสืบค้น Oracle จะเริ่มการประเมินจากซ้ายไปขวาหรือจากบนลงล่างหากใช้วงเล็บอย่างชัดเจนลำดับอาจแตกต่างกันไปเนื่องจากวงเล็บจะได้รับลำดับความสำคัญมากกว่า ตัวดำเนินการห้อย
ข้อควรจำ -
ต้องเลือกจำนวนคอลัมน์ที่เท่ากันโดยคำสั่ง SELECT ที่เข้าร่วมทั้งหมดชื่อคอลัมน์ที่ใช้ในการแสดงผลจะนำมาจากแบบสอบถามแรก
ชนิดข้อมูลของรายการคอลัมน์ต้องเข้ากันได้ / โดยปริยายโดย oracle Oracle จะไม่ทำการแปลงประเภทโดยนัยหากคอลัมน์ที่เกี่ยวข้องในการสืบค้นคอมโพเนนต์เป็นของกลุ่มชนิดข้อมูลที่แตกต่างกันตัวอย่างเช่นถ้าคอลัมน์ในแบบสอบถามองค์ประกอบแรกเป็นประเภทข้อมูล DATE และคอลัมน์ที่เกี่ยวข้องในคิวรีคอมโพเนนต์ที่สองเป็นข้อมูล พิมพ์ CHAR Oracle จะไม่ทำการแปลงโดยนัย แต่เพิ่มข้อผิดพลาด ORA-01790
ต้องใช้ลำดับตำแหน่งเพื่อจัดเรียงชุดผลลัพธ์ ไม่อนุญาตให้จัดลำดับชุดผลลัพธ์แต่ละรายการกับตัวดำเนินการ Set ORDER BY สามารถปรากฏขึ้นหนึ่งครั้งที่ส่วนท้ายของแบบสอบถาม ตัวอย่างเช่น,
ตัวดำเนินการ UNION และ INTERSECT เป็นตัวสับเปลี่ยนกล่าวคือลำดับของการสืบค้นไม่สำคัญ มันไม่ได้เปลี่ยนผลลัพธ์สุดท้าย
ประสิทธิภาพที่ชาญฉลาด UNION ALL แสดงประสิทธิภาพที่ดีกว่าเมื่อเทียบกับ UNION เนื่องจากทรัพยากรไม่สูญเปล่าในการกรองรายการที่ซ้ำกันและจัดเรียงชุดผลลัพธ์
ตัวดำเนินการตั้งสามารถเป็นส่วนหนึ่งของการสืบค้นย่อย
ไม่สามารถใช้ตัวดำเนินการตั้งค่าในคำสั่ง SELECT ที่มีนิพจน์คอลเลคชัน TABLE
ไม่อนุญาตให้ใช้ LONG, BLOB, CLOB, BFILE, VARRAY หรือตารางที่ซ้อนกันในตัวดำเนินการตั้งค่าสำหรับคำสั่งอัพเดตไม่อนุญาตให้ใช้กับตัวดำเนินการ set
ยูเนี่ยน
เมื่อมีการรวมคิวรี SELECT หลายรายการโดยใช้ตัวดำเนินการ UNION Oracle จะแสดงผลลัพธ์ที่รวมกันจากการสืบค้น SELECT แบบผสมทั้งหมดหลังจากลบรายการที่ซ้ำกันทั้งหมดและเรียงตามลำดับ (จากน้อยไปหามากตามค่าเริ่มต้น) โดยไม่สนใจค่า NULL
พิจารณาคำค้นหาห้าข้อด้านล่างที่เข้าร่วมโดยใช้ตัวดำเนินการ UNION ชุดผลลัพธ์ที่รวมกันสุดท้ายประกอบด้วยค่าจาก SQL ทั้งหมด สังเกตการลบการทำซ้ำและการจัดเรียงข้อมูล
SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;
NUM
-------
1
3
5
6
เพื่อให้สังเกตคอลัมน์ที่เลือกในแบบสอบถาม SELECT ต้องเป็นชนิดข้อมูลที่เข้ากันได้ Oracle แสดงข้อความแสดงข้อผิดพลาดเมื่อละเมิดกฎ
SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;
SELECT TO_DATE('12-OCT-03') FROM DUAL
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
ยูเนี่ยนทั้งหมด
UNION และ UNION ALL มีความคล้ายคลึงกันในการทำงานโดยมีความแตกต่างเล็กน้อย แต่ UNION ALL ให้ชุดผลลัพธ์โดยไม่ต้องลบการทำซ้ำและจัดเรียงข้อมูล ตัวอย่างเช่นในข้อความค้นหาด้านบน UNION จะถูกแทนที่ด้วย UNION ALL เพื่อดูเอฟเฟกต์
พิจารณาแบบสอบถามที่แสดงในส่วน UNION สังเกตความแตกต่างของผลลัพธ์ที่สร้างขึ้นโดยไม่มีการเรียงลำดับและการคัดลอกข้อมูลซ้ำซ้อน
SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;
NUM
-------
1
5
3
6
3
ตัด
การใช้ตัวดำเนินการ INTERSECT Oracle จะแสดงแถวทั่วไปจากทั้งคำสั่ง SELECT โดยไม่มีข้อมูลที่ซ้ำกันและจัดเรียงตามลำดับ (จากน้อยไปมากตามค่าเริ่มต้น)
ตัวอย่างเช่นแบบสอบถาม SELECT ด้านล่างจะดึงข้อมูลเงินเดือนซึ่งเป็นเรื่องปกติในแผนก 10 และ 20 ตามมาตรฐาน ISO SQL INTERSECT จะเหนือกว่าคนอื่น ๆ ตามลำดับความสำคัญของการประเมินตัวดำเนินการชุด แต่ Oracle ยังไม่รวมอยู่ด้วย
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20
SALARY
---------
1500
1200
2000
ลบ
ตัวดำเนินการลบแสดงแถวที่มีอยู่ในแบบสอบถามแรก แต่ไม่มีอยู่ในแบบสอบถามที่สองโดยไม่มีข้อมูลที่ซ้ำกันและจัดเรียงข้อมูลจากน้อยไปหามากตามค่าเริ่มต้น
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;
JOB_ID
-------------
HR
FIN
ADMIN
จับคู่คำสั่ง SELECT
อาจมีสถานการณ์ที่คำสั่ง SELECT แบบผสมอาจมีจำนวนและชนิดข้อมูลที่แตกต่างกันของคอลัมน์ที่เลือก ดังนั้นเพื่อให้ตรงกับรายการคอลัมน์อย่างชัดเจนคอลัมน์ NULL จะถูกแทรกในตำแหน่งที่ขาดหายไปเพื่อให้ตรงกับจำนวนและชนิดข้อมูลของคอลัมน์ที่เลือกในแต่ละคำสั่ง SELECT สำหรับคอลัมน์ตัวเลขยังสามารถแทนที่ศูนย์เพื่อให้ตรงกับประเภทของคอลัมน์ที่เลือกในแบบสอบถาม
ในคำค้นหาด้านล่างประเภทข้อมูลของชื่อพนักงาน (varchar2) และรหัสสถานที่ (หมายเลข) ไม่ตรงกัน ดังนั้นการดำเนินการค้นหาด้านล่างจะทำให้เกิดข้อผิดพลาดเนื่องจากปัญหาความเข้ากันได้
SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
อย่างชัดเจนสามารถจับคู่คอลัมน์ได้โดยการแทนที่ NULL สำหรับรหัสสถานที่และชื่อพนักงาน
SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;
การใช้คำสั่ง ORDER BY ในการดำเนินการ SET
คำสั่ง ORDER BY สามารถปรากฏได้เพียงครั้งเดียวในตอนท้ายของแบบสอบถามที่มีคำสั่ง SELECT แบบผสมหมายความว่าคำสั่ง SELECT แต่ละคำสั่งไม่สามารถมี ORDER BY clause ได้ นอกจากนี้การเรียงลำดับสามารถขึ้นอยู่กับคอลัมน์ที่ปรากฏในแบบสอบถาม SELECT แรกเท่านั้น ด้วยเหตุนี้ขอแนะนำให้จัดเรียงคิวรีแบบผสมโดยใช้ตำแหน่งคอลัมน์
แบบสอบถามคำนวณด้านล่างรวมผลลัพธ์จากสองแผนกและจัดเรียงตามคอลัมน์ SALARY
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=10
UNION
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=20
ORDER BY 3;