Trong bài viết này, chúng ta hãy xem các toán tử tập hợp SQL có sẵn trong Oracle.
Thiết lập môi trường
Các ví dụ trong bài viết này yêu cầu phải có các bảng sau đây.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Các bảng này là một biến thể của các bảng EMP và DEPT từ lược đồ SCOTT. Bạn sẽ thấy rất nhiều ví dụ của Oracle trên internet bằng cách sử dụng các bảng từ lược đồ SCOTT. Bạn có thể tìm thấy các định nghĩa bảng gốc trong tập lệnh "$ ORACLE_HOME/rdbms/admin/utlsampl.sql".
Giới thiệu
Toán tử tập hợp cho phép bạn kết hợp các kết quả của nhiều truy vấn riêng biệt thành một tập kết quả duy nhất.
Hai truy vấn sau đây sẽ được sử dụng cho hầu hết các ví dụ trong bài viết này. Cái đầu tiên trả về các bộ phận 10, 20 và 30. Thứ hai trả về các bộ phận 20, 30 và 40. Như bạn có thể thấy, các bộ phận 20 và 30 là chung cho cả hai bộ kết quả.
-- Department 10, 20 và 30.
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
3 rows selected.
SQL>
--Department 20, 30 và 40.
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
3 rows selected.
SQL>
UNION
Toán tử
UNION trả về tất cả các hàng riêng biệt bởi một trong hai câu hỏi được chọn. Điều đó có nghĩa là bất kỳ hàng trùng lặp sẽ được loại bỏ.
Trong ví dụ dưới đây, thông báo chỉ có một hàng duy nhất cho mỗi bộ phận 20 và 30, thay vì hai hàng.
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
UNION
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
4 rows selected.
SQL>
Việc loại bỏ các bản sao đòi hỏi phải xử lý thêm, vì vậy bạn nên cân nhắc sử dụng
UNION ALL nếu có thể.UNION ALL
Toán tử
UNION ALL trả về tất cả các hàng bởi một trong hai câu hỏi được chọn. Điều đó có nghĩa là bất kỳ bản sao nào sẽ vẫn còn trong tập kết quả cuối cùng.
Trong ví dụ dưới đây, chú ý có hai hàng cho mỗi bộ phận 20 và 30.
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
40 OPERATIONS
6 rows selected.
SQL>
INTERSECT
INTERSECT trả về tất cả các hàng riêng biệt bởi cả hai truy vấn lựa chọn. Điều đó có nghĩa là chỉ những hàng chung cho cả hai truy vấn sẽ có mặt trong tập kết quả cuối cùng.
Trong ví dụ dưới đây, chú ý có một hàng cho các bộ phận 20 và 30, vì cả hai hàng này đều xuất hiện trong tập kết quả cho các truy vấn tương ứng của chúng.
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
INTERSECT
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
2 rows selected.
SQL>
MINUS
MINUS trả về tất cả các hàng riêng biệt bằng cách truy vấn đầu tiên nhưng không phải là thứ hai được chọn. Đây là chức năng tương đương với toán tử ANSI set EXCEPT DISTINCT.
Trong ví dụ dưới đây, truy vấn đầu tiên sẽ trả về các bộ phận 10, 20, 30, nhưng các bộ phận 20 và 30 bị xóa vì chúng được trả về bởi truy vấn thứ hai. Điều này để lại một hàng duy nhất cho bộ phận 10.
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
MINUS
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
1 row selected.
SQL>
ORDER BY
ORDER BY được áp dụng cho tất cả các hàng trả lại trong tập kết quả cuối cùng. Các cột trong mệnh đề ORDER BY có thể được tham chiếu bằng tên cột hoặc bí danh cột có trong truy vấn đầu tiên của câu lệnh, vì chúng được chuyển đến tập kết quả cuối cùng. Thông thường, bạn sẽ thấy mọi người sử dụng vị trí cột vì nó ít gây nhầm lẫn khi dữ liệu được lấy từ các vị trí khác nhau cho mỗi khối truy vấn.-- Tên cột
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
7782 CLARK
7839 KING
7934 MILLER
6 rows selected.
SQL>
-- Bí danh cột
SELECT employee_id AS emp_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY emp_id;
EMP_ID EMPLOYEE_NAME
---------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
7782 CLARK
7839 KING
7934 MILLER
6 rows selected.
SQL>
-- Vị trí cột
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
7782 CLARK
7839 KING
7934 MILLER
6 rows selected.
SQL>
Tham chiếu tên cột hoặc bí danh cột trong các truy vấn sau này không được phép.
-- Tên cột sai.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY department_id;
ORDER BY department_id
*
ERROR at line 8:
ORA-00904: "DEPARTMENT_ID": invalid identifier
SQL>
-- Wrong column alias
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id AS dept_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY dept_id;
ORDER BY dept_id
*
ERROR at line 8:
ORA-00904: "DEPT_ID": invalid identifier
SQL>
Loại dữ liệu
Các kiểu dữ liệu phải khớp với cùng một vị trí cột trong mỗi truy vấn. Một số chuyển đổi ngầm sẽ có thể giữa một số loại dữ liệu, ví dụ
CHAR/ VARCHAR2 hoặc chuyển đổi giữa một số loại số.
Trong ví dụ sau, chúng tôi chuyển đổi cột EMPLOYEE_ID sang
VARCHAR2 sử dụng hàm TO_CHAR, ngắt câu lệnh.SELECT TO_CHAR(employee_id) AS employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
SELECT TO_CHAR(employee_id) AS employee_id, employee_name
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
SQL>
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam"
#OracleTutorial
#OracleDBA
#OracleDatabaseAdministration
#học oracle database
#oca
#ocp
#oce
#ocm
