|
1、集合操作符概述 - 除了union all，重复行会被自动删除
- 除了union all，自动以第一列升序排序
- 集合操作结果的列名来自于第一个查询
2、union操作符 把两个查询的结果加起来并去掉重复的记录 例子：查询员工在公司担任过的所有职位 SQL> select employee_id,job_id from employees 2 union 3 select employee_id,job_id from job_history; EMPLOYEE_ID JOB_ID ----------- ---------- 206 AC_ACCOUNT 115 rows selected. 这里返回115行，说明有部分是重复的，也就是有的员工同一个职位做过2次，被去掉了，同时结果默认对第一列升序排序，由于排序会使用PGA里面的排序区或者临时把空间，如果生产环境中有大量的集合操作，要注意临时表空间的增长情况。
3、union all操作符 把两个查询的结果加起来并保留重复的记录 例子：查询员工在公司呆过的所有部门 SQL> select employee_id,job_id,department_id from employees 2 union all 3 select employee_id,job_id,department_id from job_history 4 order by employee_id; EMPLOYEE_ID JOB_ID DEPARTMENT_ID ----------- ---------- ------------- 206 AC_ACCOUNT 110 117 rows selected. union all默认不会对结果进行排序，所有如果明确两个查询结果没有重复的，可以使用union all，避免排序操作及临时表空间的使用，这也是性能调优方法之一。
4、intersect操作符 把两个查询的结果取相同的部分，会去重及排序 例子：查询当前职位和以前从事过的职位相同的员工编号及职位 SQL> select employee_id,job_id from employees 2 intersect 3 select employee_id,job_id from job_history; EMPLOYEE_ID JOB_ID ----------- ---------- 176 SA_REP 200 AD_ASST 使用去重的功能还可以通过集合操作取出唯一的记录，类似distinct 例子：查询人员表里面有多少唯一的部门 SQL> select department_id from employees 2 intersect 3 select department_id from employees; DEPARTMENT_ID ------------- 10 12 rows selected. SQL> select department_id from employees 2 union 3 select department_id from employees; DEPARTMENT_ID ------------- 10 12 rows selected.
5、minus操作符 从一个查询的结果里面除去另一个查询的结果，会去重及排序 例子：查询没有改变过职位的人员 SQL> select employee_id from employees 2 minus 3 select employee_id from job_history; EMPLOYEE_ID ----------- 206 100 rows selected.
6、集合操作符注意事项 - 每个集合select子句的字段，个数和类型必须匹配
- 集合的默认执行顺序是从上到下，可以使用括号去改变
- order by子句只能放在集合的最后，排序的字段或者字段别名只能来自于第一个select子句，或者直接使用位置进行排序
- 除了union all，其他集合操作符都会对结果进行去重和升序排序
- 最终结果的字段来自于第一个查询
例子：select子句字段个数不匹配会报错，可以使用null补齐 SQL> select employee_id,job_id,department_id from employees 2 union 3 select employee_id,job_id from job_history; select employee_id,job_id,department_id from employees * ERROR at line 1: ORA-01789: query block has incorrect number of result columns SQL> select employee_id,job_id,department_id from employees 2 union 3 select employee_id,job_id,null from job_history; EMPLOYEE_ID JOB_ID DEPARTMENT_ID ----------- ---------- ------------- 100 AD_PRES 90 例子：select子句字段类型不匹配会报错 SQL> select employee_id,job_id,department_id from employees 2 union 3 select employee_id,job_id,sysdate from job_history; select employee_id,job_id,department_id from employees * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression 可以使用转换函数进行转换，保证类型一致 SQL> select department_id,to_number(null) location,hire_date from employees 2 union 3 select department_id,location_id,to_date(null) from departments; DEPARTMENT_ID LOCATION HIRE_DATE ------------- ---------- ------------ 10 1700 如果是数字类型，也可以直接使用数字进行填充 SQL> select employee_id,job_id,salary from employees 2 union 3 select employee_id,job_id,0 from job_history; EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 100 AD_PRES 24000 101 AC_ACCOUNT 0 例子：使用括号改变执行顺序 SQL> select employee_id,job_id,department_id from employees 2 union 3 select employee_id,job_id,department_id from job_history 4 minus 5 select employee_id,job_id,department_id from job_history 6 union all 7 select employee_id,job_id,department_id from employees; EMPLOYEE_ID JOB_ID DEPARTMENT_ID ----------- ---------- ------------- 197 SH_CLERK 50 213 rows selected. SQL> ( 2 select employee_id,job_id,department_id from employees 3 union 4 select employee_id,job_id,department_id from job_history 5 ) 6 minus 7 ( 8 select employee_id,job_id,department_id from job_history 9 union all 10 select employee_id,job_id,department_id from employees 11 ); no rows selected 例子：order by子句不在最后会报错 SQL> ( 2 select employee_id,job_id,department_id from employees 3 union 4 select employee_id,job_id,department_id from job_history 5 order by 1 6 ) 7 minus 8 ( 9 select employee_id,job_id,department_id from job_history 10 union all 11 select employee_id,job_id,department_id from employees 12 ); order by 1 * ERROR at line 5: ORA-00907: missing right parenthesis 例子：控制集合最后结果的顺序 SQL> select 'sing' as "my dream",3 a_dummy from dual 2 union 3 select 'I''d like to teach',1 a_dummy from dual 4 union 5 select 'the world to',2 a_dummy from dual; my dream A_DUMMY ----------------- ---------- I'd like to teach 1 sing 3 the world to 2 SQL> select 'sing' as "my dream",3 a_dummy from dual 2 union 3 select 'I''d like to teach',1 a_dummy from dual 4 union 5 select 'the world to',2 a_dummy from dual 6 order by a_dummy; my dream A_DUMMY ----------------- ---------- I'd like to teach 1 the world to 2 sing 3 SQL> column a_dummy noprint SQL> select 'sing' as "my dream",3 a_dummy from dual 2 union 3 select 'I''d like to teach',1 a_dummy from dual 4 union 5 select 'the world to',2 a_dummy from dual 6 order by a_dummy; my dream ----------------- I'd like to teach the world to sing 例子：Oracle外连接的实现方式 SQL> select employee_id,last_name,department_name 2 from employees e,departments d 3 where e.department_id=d.department_id(+) 4 union 5 select employee_id,last_name,department_name 6 from employees e,departments d 7 where e.department_id(+)=d.department_id; EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ Treasury 123 rows selected.
7、相关习题 （1）View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement: SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 ;Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.) A.ORDER BY 3; B.ORDER BY 'b'; C.ORDER BY DEPT_ID; D.ORDER BY DEPT_NAME; 答案：AC B不正确的原因是因为'b'不是列的名字，也不是别名，而是一个具体的值,所以不能在order by 后面使用。 （2）View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? SELECT department_id FROM departments ____ SELECT department_id FROM employees WHERE manager_id IS NULL? A.UNION B.MINUS C.INTERSECT D.UNION ALL 答案：B （3）View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? SELECT location_id, city FROM locations ____ SELECT location_id, city FROM locations JOIN departments USING(location_id)? A.UNION B.MINUS C.INTERSECT D.UNION ALL 答案：C （4）View the Exhibit and examine the data in the EMPLOYEES tables. Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id=90 UNION SELECT employee_id, department_id FROM employees WHERE department_id=10；What would be the outcome of the above SQL statement？ A.The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
B.The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
C.The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
D.The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement. 答案：D （5）View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure. Choose the correct SET operator to fill in the blank space and complete the following query. SELECT employee_id FROM employees WHERE job_id = 'SA_MAN' ___________ SELECT employee_id FROM job_history WHERE job_id='SA_MAN'； A.UNION
B.MINUS
C.INTERSECT
D.UNION ALL 答案：A
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-11-25 09:36:43
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对