将多个查询用 SET 操作符连接组成一个新的查询
- UNION/UNION ALL
- INTERSECT
- MINUS
- 排序:ORDER BY
UNION 操作符
- UNION 操作符返回两个查询的结果集的并集
SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history;
UNION ALL 操作符
- UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
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;
INTERSECT 操作符
- INTERSECT 操作符返回两个结果集的交集
SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;
MINUS 操作符
- MINUS操作符:返回两个结果集的差集
SELECT employee_id,job_id FROM employees MINUS SELECT employee_id,job_id FROM job_history;
使用 SET 操作符注意事项
- 在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
- 括号可以改变执行的顺序
- ORDER BY 子句:只能在语句的最后出现,可以使用第一个查询中的列名, 别名或相对位置
SET 操作符
- 除 UNION ALL之外,系统会自动将重复的记录删除
- 系统将第一个查询的列名显示在输出中
- 除 UNION ALL之外,系统自动按照第一个查询中的第一个列的升序排列
匹配各SELECT 语句
SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments;
SELECT employee_id, job_id,salary FROM employees UNION SELECT employee_id, job_id,0 FROM job_history;
使用相对位置排序
COLUMN a_dummy NOPRINT SELECT 'sing' AS "My dream", 3 a_dummy FROM dual UNION SELECT 'I`d like to teach', 1 FROM dual UNION SELECT 'the world to', 2 FROM dual ORDER BY 2;