Sử dụng toán tử bộ
Toán tử tập hợp được sử dụng để nối kết quả của hai (hoặc nhiều) câu lệnh SELECT. Các toán tử SET có sẵn trong Oracle 11g là UNION, UNION ALL, INTERSECT và MINUS.
Toán tử tập hợp UNION trả về kết quả kết hợp của hai câu lệnh SELECT. Về cơ bản, nó loại bỏ các bản sao khỏi kết quả, tức là chỉ một hàng sẽ được liệt kê cho mỗi kết quả trùng lặp. Để chống lại hành vi này, hãy sử dụng toán tử tập hợp UNION ALL để giữ lại các bản sao trong kết quả cuối cùng .INTERSECT chỉ liệt kê các bản ghi chung cho cả các truy vấn SELECT; toán tử bộ MINUS loại bỏ kết quả của truy vấn thứ hai khỏi đầu ra nếu chúng cũng được tìm thấy trong kết quả của truy vấn đầu tiên. Các phép toán tập hợp INTERSECT và MINUS tạo ra kết quả không trùng lặp.
Tất cả các toán tử SET đều có cùng mức độ ưu tiên giữa chúng, thay vào đó, trong quá trình thực thi truy vấn, Oracle bắt đầu đánh giá từ trái sang phải hoặc từ trên xuống dưới. các toán tử lủng lẳng.
Những điểm cần nhớ -
Tất cả các câu lệnh SELECT tham gia phải chọn cùng một số cột. Tên cột được sử dụng trong màn hình được lấy từ truy vấn đầu tiên.
Các kiểu dữ liệu của danh sách cột phải tương thích / có thể chuyển đổi hoàn toàn bằng oracle. Oracle sẽ không thực hiện chuyển đổi kiểu ngầm định nếu các cột tương ứng trong truy vấn thành phần thuộc các nhóm kiểu dữ liệu khác nhau. Ví dụ: nếu một cột trong truy vấn thành phần đầu tiên thuộc loại dữ liệu DATE và cột tương ứng trong truy vấn thành phần thứ hai là dữ liệu gõ CHAR, Oracle sẽ không thực hiện chuyển đổi ngầm, nhưng làm tăng lỗi ORA-01790.
Thứ tự vị trí phải được sử dụng để sắp xếp tập kết quả. Không cho phép đặt hàng tập hợp kết quả riêng lẻ với toán tử Đặt. ORDER BY có thể xuất hiện một lần ở cuối truy vấn. Ví dụ,
Toán tử UNION và INTERSECT có tính chất giao hoán, tức là thứ tự của các truy vấn không quan trọng; nó không thay đổi kết quả cuối cùng.
Hiệu suất khôn ngoan, UNION ALL cho thấy hiệu suất tốt hơn so với UNION vì tài nguyên không bị lãng phí trong việc lọc các bản sao và sắp xếp tập hợp kết quả.
Toán tử tập hợp có thể là một phần của truy vấn con.
Không thể sử dụng toán tử tập hợp trong câu lệnh SELECT chứa biểu thức tập hợp TABLE.
LONG, BLOB, CLOB, BFILE, VARRAY hoặc bảng lồng nhau không được phép sử dụng trong toán tử Set. Đối với mệnh đề cập nhật không được phép với các toán tử tập hợp.
LIÊN HIỆP
Khi nhiều truy vấn SELECT được kết hợp bằng toán tử UNION, Oracle sẽ hiển thị kết quả tổng hợp từ tất cả các truy vấn SELECT phức hợp, sau khi loại bỏ tất cả các bản sao và theo thứ tự được sắp xếp (tăng dần theo mặc định) mà không bỏ qua các giá trị NULL.
Hãy xem xét năm truy vấn dưới đây được kết hợp bằng toán tử UNION. Tập kết quả kết hợp cuối cùng chứa giá trị từ tất cả các SQL. Lưu ý loại bỏ trùng lặp và sắp xếp dữ liệu.
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
6Cần lưu ý, các cột được chọn trong các truy vấn CHỌN phải có kiểu dữ liệu tương thích. Oracle đưa ra một thông báo lỗi khi quy tắc bị vi phạm.
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ĐOÀN KẾT TẤT CẢ
UNION và UNION ALL hoạt động tương tự nhau với một chút khác biệt. Nhưng UNION ALL đưa ra tập hợp kết quả mà không cần loại bỏ trùng lặp và sắp xếp dữ liệu. Ví dụ, trong truy vấn trên UNION được thay thế bằng UNION ALL để xem hiệu quả.
Hãy xem xét truy vấn được trình bày trong phần UNION. Lưu ý sự khác biệt trong đầu ra được tạo ra mà không cần phân loại và khử trùng lặp.
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
3GIAO NHAU
Sử dụng toán tử INTERSECT, Oracle hiển thị các hàng chung từ cả hai câu lệnh SELECT, không có bản sao và dữ liệu được sắp xếp theo thứ tự được sắp xếp (tăng dần theo mặc định).
Ví dụ: truy vấn SELECT bên dưới truy xuất mức lương thường gặp ở bộ phận 10 và 20. Theo Tiêu chuẩn ISO SQL, INTERSECT nằm trên các câu hỏi khác để ưu tiên đánh giá các toán tử tập hợp nhưng điều này vẫn không được Oracle kết hợp.
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20
SALARY
---------
1500
1200
2000DẤU TRỪ
Toán tử trừ hiển thị các hàng có trong truy vấn đầu tiên nhưng vắng mặt trong truy vấn thứ hai, không có bản sao và dữ liệu được sắp xếp theo thứ tự tăng dần theo mặc định.
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;
JOB_ID
-------------
HR
FIN
ADMINKhớp với câu lệnh SELECT
Có thể có các tình huống trong đó các câu lệnh SELECT kết hợp có thể có số lượng và kiểu dữ liệu khác nhau của các cột đã chọn. Do đó, để khớp danh sách cột một cách rõ ràng, các cột NULL được chèn vào các vị trí còn thiếu để khớp với số lượng và kiểu dữ liệu của các cột đã chọn trong mỗi câu lệnh SELECT. Đối với cột số, số 0 cũng có thể được thay thế để phù hợp với loại cột được chọn trong truy vấn.
Trong truy vấn dưới đây, kiểu dữ liệu của tên nhân viên (varchar2) và id vị trí (số) không khớp. Do đó, việc thực thi truy vấn dưới đây sẽ phát sinh lỗi do vấn đề tương thích.
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 expressionRõ ràng, các cột có thể được so khớp bằng cách thay thế NULL cho id vị trí và tên Nhân viên.
SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;Sử dụng mệnh đề ORDER BY trong hoạt động SET
Mệnh đề ORDER BY chỉ có thể xuất hiện một lần ở cuối truy vấn chứa các câu lệnh SELECT phức hợp, ngụ ý rằng các câu lệnh SELECT riêng lẻ không thể có mệnh đề ORDER BY. Ngoài ra, việc sắp xếp có thể dựa trên các cột chỉ xuất hiện trong truy vấn SELECT đầu tiên. Vì lý do này, bạn nên sắp xếp truy vấn ghép bằng cách sử dụng các vị trí cột.
Truy vấn tổng hợp bên dưới thống nhất kết quả từ hai bộ phận và sắp xếp theo cột LƯƠNG.
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;