5.11使用集合操作

Oracle 集合操作

SQL结果集的集合操作：并集、交集、差集

A.UNION并集

1.UNION：去除重复记录

结果中的总记录数可能 < employees的记录数+job_history的记录数

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

2.UNION ALL 保留重复记录

结果中的总记录数一定 = employees的记录数+job_history的记录数

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;

B.INTERSECT交集

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;

C.MINUS差集

SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history;