SQL基础之使用集合运算符进行多表查询（十二）

徐冬丽

　　集合操作：类型和注意事项

　　集合操作注意事项

• 　　在 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>　　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