10-SET运算符
将多个查询用 SET 操作符连接组成一个新的查询
-UNION/UNION ALL
-INTERSECT
-MINUS
排序:ORDER BY
1 --UNION： 操作符返回两个查询的结果集的并集
2 SELECT employee_id, job_id
3 FROM employees
4 UNION
5 SELECT employee_id, job_id
6 FROM job_history;
7
8 --UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
9 SELECT employee_id, job_id, department_id
10 FROM employees
11 UNION ALL
12 SELECT employee_id, job_id, department_id
13 FROM job_history
14 ORDER BY employee_id;
15
16 --INTERSECT 操作符返回两个结果集的交集
17 SELECT employee_id, job_id
18 FROM employees
19 INTERSECT
20 SELECT employee_id, job_id
21 FROM job_history;
22
23 --MINUS操作符：返回两个结果集的差集
24 SELECT employee_id,job_id
25 FROM employees
26 MINUS
27 SELECT employee_id,job_id
28 FROM job_history;
使用 SET 操作符注意事项
*在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
*括号可以改变执行的顺序
*ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
*除 UNION ALL之外，系统会自动将重复的记录删除
*系统将第一个查询的列名显示在输出中
*除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列
1 --匹配各SELECT 语句举例1
2 SELECT department_id, TO_NUMBER(null)
3 location, hire_date
4 FROM employees
5 UNION
6 SELECT department_id, location_id, TO_DATE(null)
7 FROM departments;
8
9 --匹配各SELECT 语句举例2
10 SELECT employee_id, job_id,salary
11 FROM employees
12 UNION
13 SELECT employee_id, job_id,0
14 FROM job_history;
15
16 --使用相对位置排序举例
17 COLUMN a_dummy NOPRINT --查询结果不显示a_dummy列
18 SELECT 'sing' AS "My dream", 1 a_dummy
19 FROM dual
20 UNION
21 SELECT 'I`d like to teach', 2
22 FROM dual
23 UNION
24 SELECT 'the world to',3
25 FROM dual
26 ORDER BY 2 asc;
eg：
1 --查询部门的部门号，其中不包括job_id是”ST_CLERK”的部门号
2 --select department_id
3 --from departments
4 --where department_id not in (
5 -- select distinct department_id
6 -- from employees
7 -- where job_id = 'ST_CLERK'
8 -- )
9 select department_id
10 from departments
11 minus
12 select department_id
13 from employees
14 where job_id = 'ST_CLERK';
15
16 --查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列
17 column a_dummy noprint;
18 SELECT job_id,department_id,1 a_dummy
19 from employees
20 where department_id = 10
21 union
22 SELECT job_id,department_id,2
23 from employees
24 where department_id = 50
25 union
26 SELECT job_id,department_id,3
27 from employees
28 where department_id = 20
29 order by 3 asc;
30
31 --查询所有员工的last_name ,department_id 和department_name
32 select last_name,department_id,to_char(null)
33 from employees
34 union
35 select to_char(null),department_id,department_name
36 from departments;
