Oracle 之集合操作

union ：就是两个集合相加重叠部分不重加，并会进行简单的排序

union all ：就是单纯的加不去重不排序

intersect ： 就是将两个集合重复部分取出来

minus ：就是用A减去A和B的公共部分

 

 

SQL> desc job_history

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)

START_DATE NOT NULL DATE

END_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

DEPARTMENT_ID NUMBER(4)

 

SQL> select * from job_history;

 

EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID

----------- --------- --------- ---------- -------------

102 13-JAN-01 24-JUL-06 IT_PROG 60

101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110

101 28-OCT-01 15-MAR-05 AC_MGR 110

201 17-FEB-04 19-DEC-07 MK_REP 20

114 24-MAR-06 31-DEC-07 ST_CLERK 50

122 01-JAN-07 31-DEC-07 ST_CLERK 50

200 17-SEP-95 17-JUN-01 AD_ASST 90

176 24-MAR-06 31-DEC-06 SA_REP 80

176 01-JAN-07 31-DEC-07 SA_MAN 80

200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90

 

10 rows selected.

 

SQL> select employee_id,job_id from employees;

 

107 rows selected.

 

SQL> select employee_id,job_id from employees

2 union

3 select employee_id,job_id from job_history;

 

115 rows selected.

 

注意union 语句必须前后对应 且数据类型相同，不同时要进行转换转换不成功则报错

转换可用 to_date(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,from job_history

*

ERROR at line 3:

ORA-00936: missing expression

 

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

101 AC_ACCOUNT

101 AC_MGR

101 AD_VP 90

 

 

SQL> select distinct department_id from employees;

 

DEPARTMENT_ID

-------------

100

30

 

20

70

90

110

50

40

80

10

 

DEPARTMENT_ID

-------------

60

 

12 rows selected.

 

等同于如下，并且进行了排序

 

SQL> select department_id from employees

2 intersect

3 select department_id from employees;

 

DEPARTMENT_ID

-------------

10

20

30

40

50

60

70

80

90

100

110

 

DEPARTMENT_ID

-------------

 

 

12 rows selected.

 

 

查询从未改动过工作的人员，用minus 即减去改动记录就是未改动人员

select employee_id from employees

minus

select employee_id from job_history

 

 

select 'sing' as "my dream",3 a_dummy

from dual

union

select 'i''d like to teach' ,1 a_dummy

from dual

union

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

 

小技巧; a加语句就是在上一个语句最后一行，结尾加命令 操作如下：

SQL> a order by a_dummy

9* order by a_dummy

SQL> r

1 select 'sing' as "my dream",3 a_dummy

2 from dual

3 union

4 select 'i''d like to teach' ,1 a_dummy

5 from dual

6 union

7 select 'the world to',2 a_dummy

8 from dual

9* order by a_dummy

 

my dream A_DUMMY

----------------- ----------

i'd like to teach 1

the world to 2

sing 3