ITPub博客

首页 > 数据库 > Oracle > OCP课程9:SQL之使用集合操作符

OCP课程9:SQL之使用集合操作符

原创 Oracle 作者:stonebox1122 时间:2015-11-25 09:16:18 0 删除 编辑

1、集合操作符概述

clipboard

clipboard[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-1846035/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-13

  • 博文量
    204
  • 访问量
    1080780