การใช้ 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;