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