集合操作：类型和注意事项
集合操作注意事项
在 SELECT 列表中的列名和表达式在数量上必须匹配
第二个查询中的每一列的数据类型必须与第一个查询其对应的列的数据类型相匹配
可以用括号来改变的执行顺序。
ORDER BY 子句:
– 只能在语句的最后出现
– 可以使用第一个查询中的列名，别名或相对位置
Oracle 服务器和集合操作符
除 UNION ALL之外，系统会自动删除重复的记录
列名为第一个查询返回的结果
除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列
使用 UNION 操作符
1、显示当前和以前的工作的所有员工的详细信息。每个雇员只显示一次。
select employee_id, job_id
from employees
union
select employee_id, job_id
from job_history;
使用 UNION ALL 操作符
1、显示当前和以前的员工的所有部门。
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 操作符
显示员工ID和工作ID，当前的职称相同(也就是说,他们换工作但是现在已经回到以前同样的工作)。
select employee_id, job_id
from employees
intersect
select employee_id, job_id
from job_history;
使用 MINUS 操作符
1、显示员工表中一次都没有改变过工作的的员工ID
select employee_id
from employees
minus
select employee_id
from job_history;
相匹配的 SELECT 语句
使用 UNION 操作符显示location_id,department_name,state_province
当字段在一个或另一个表中不存在，必须匹配上数据类型（使用TO_CHAR函数或其他转换函数）
select location_id, department_name "Department",
to_char(null) "warehouse location"
from departments
union
select location_id, to_char(null) "Department",
state_province
from locations;
使用UNION操作符，显示雇员的ID，工作ID，和所有员工的工资
select employee_id, job_id,salary
from employees
union
select employee_id, job_id,0
from job_history;
集合操作中使用 ORDER BY 子句的注意事项
复合查询中 ORDER BY 子句只能在结束时出现一次
集合操作中每个查询不能有单独的 ORDER BY 子句
ORDER BY 子句中 只能识别第一个 SELECT 查询的列。
默认情况下，第一列的第一个 SELECT 查询使用升序对输出进行排序。
请查询出所有的部门下没有 ST_CLERK 工种的 department_id，要求使用集合操作符
select department_id
from departments
minus
select department_id
from employees
where job_id not like 'ST_CLERK';
2、请使用集合操作符写一条 SQL，查出所有的没有部门坐落的国家的 country_id,country_name
select country_id,country_name
from countries
minus
select l.country_id,c.country_name
from locations l join countries c
on (l.country_id=c.country_id)
join departments d
on d.location_id=l.location_id;
3、请使用集合操作符写一条 SQL，查出部门号在 10，50，20 中的所有的 job_id,department_id，
并以 10,50,20 的排列顺序显示。
select distinct job_id,department_id
from employees
where department_id = 10
union all
select distinct job_id,department_id
from employees
where department_id= 50
union all
select distinct job_id,department_id
from employees
where department_id= 20;
4、请查出所有工作发生过变动，但是多次变动后现在的工作是做的以前做过的工作的员工的employee_id 和 job_id
select employee_id,job_id
from employees
intersect
select employee_id,job_id
from job_history;
5、HR 部门的同事希望你能够帮助他们生成一个报告，要求满足以下 2 点：
a)从 EMPLOYEES 表中获得所有员工的 last_name 和 department_id（不管是否属于同一个部门）
b)从 DEPARTMENTS 表中获得所有的 department_id 和 department_name（不管是否该部门有员工）
select last_name,department_id,to_char(null) dept_name
from employees
union
select to_char(null),department_id,department_name
from departments;
LAST_NAME DEPARTMENT_ID DEPT_NAME
------------------------- ------------- ------------------------------
Abel 80
Ande 80
Atkinson 50
Austin 60
Baer 70
Baida 30
Banda 80
Bates 80
Bell 50
Bernstein 80
Bissot 50
Bloom 80
Bull 50
Cabrio 50
Cambrault 80
Chen 100
Chung 50
Colmenares 30
Davies 50
De Haan 90
Dellinger 50
Dilly 50
Doran 80
Ernst 60
Errazuriz 80
Everett 50
Faviet 100
Fay 20
Feeney 50
Fleaur 50
Fox 80
Fripp 50
Gates 50
Gee 50
Geoni 50
Gietz 110
Grant 50
Grant
Greenberg 100
Greene 80
Hall 80
Hartstein 20
Higgins 110
Himuro 30
Hunold 60
Hutton 80
Johnson 80
Jones 50
Kaufling 50
Khoo 30
King 80
King 90
Kochhar 90
Kumar 80
Ladwig 50
Landry 50
Lee 80
Livingston 80
Lorentz 60
Mallin 50
Markle 50
Marlow 50
Marvins 80
Matos 50
Mavris 40
McCain 50
McEwen 80
Mikkilineni 50
Mourgos 50
Nayer 50
OConnell 50
Olsen 80
Olson 50
Ozer 80
Partners 80
Pataballa 60
Patel 50
Perkins 50
Philtanker 50
Popp 100
Rajs 50
Raphaely 30
Rogers 50
Russell 80
Sarchand 50
Sciarra 100
Seo 50
Sewall 80
Smith 80
Stiles 50
Sullivan 50
Sully 80
Taylor 50
Taylor 80
Tobias 30
Tucker 80
Tuvault 80
LAST_NAME DEPARTMENT_ID DEPT_NAME
------------------------- ------------- ------------------------------
Urman 100
Vargas 50
Vishney 80
Vollman 50
Walsh 50
Weiss 50
Whalen 10
Zlotkey 80
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
