SELECT语句中集合运算符

UNION/INTERSECT/MINUS

使用UNION(ALL)、INTERSECT、MINUS可以对子查询的结果集进行结合运算：

创建测试表

create table test1 as select * from emp where deptno in

(10,20);

create table test2 as select * from emp where deptno in

(20,30);

测试表test1:包含scott.emp表中10号和20号部门员工信息

SCOTT@PROD>select * from test1 order by deptno;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7839 KING       PRESIDENT            17-NOV-81       5000                    10

7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

7566 JONES      MANAGER         7839 02-APR-81       2975                    20

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

8 rows selected.

测试表test2:包含scott.emp表中20号和30号部门员工信息

SCOTT@PROD>select * from test2 order by deptno;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

7566 JONES      MANAGER         7839 02-APR-81       2975                    20

7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

7900 JAMES      CLERK           7698 03-DEC-81        950                    30

7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

11 rows selected.

UNION:两个子查询并集，去重，排序

SCOTT@PROD>select empno,ename,job,deptno from test1

unionselect empno,ename,job,deptno from test2;

EMPNO ENAME      JOB           DEPTNO

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

7369 SMITH      CLERK             20

7499 ALLEN      SALESMAN          30

7521 WARD       SALESMAN          30

7566 JONES      MANAGER           20

7654 MARTIN     SALESMAN          30

7698 BLAKE      MANAGER           30

7782 CLARK      MANAGER           10

7788 SCOTT      ANALYST           20

7839 KING       PRESIDENT         10

7844 TURNER     SALESMAN          30

7876 ADAMS      CLERK             20

7900 JAMES      CLERK             30

7902 FORD       ANALYST           20

7934 MILLER     CLERK             10

14 rows selected.

UNION ALL:两个子查询并集，不去重，不排序

SCOTT@PROD>select empno,ename,job,deptno from test1

union allselect empno,ename,job,deptno from test2;

EMPNO ENAME      JOB           DEPTNO

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

7369 SMITH      CLERK             20

7566 JONES      MANAGER           20

7782 CLARK      MANAGER           10

7788 SCOTT      ANALYST           20

7839 KING       PRESIDENT         10

7876 ADAMS      CLERK             20

7902 FORD       ANALYST           20

7934 MILLER     CLERK             10

7369 SMITH      CLERK             20

7499 ALLEN      SALESMAN          30

7521 WARD       SALESMAN          30

7566 JONES      MANAGER           20

7654 MARTIN     SALESMAN          30

7698 BLAKE      MANAGER           30

7788 SCOTT      ANALYST           20

7844 TURNER     SALESMAN          30

7876 ADAMS      CLERK             20

7900 JAMES      CLERK             30

7902 FORD       ANALYST           20

19 rows selected.

INTERSECT:两个子查询交集，去重，排序

SCOTT@PROD>select empno,ename,job,deptno from test1

intersectselect empno,ename,job,deptno from test2;

EMPNO ENAME      JOB           DEPTNO

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

7369 SMITH      CLERK             20

7566 JONES      MANAGER           20

7788 SCOTT      ANALYST           20

7876 ADAMS      CLERK             20

7902 FORD       ANALYST           20

MINUS:两个子查询差集，去重，排序

SCOTT@PROD>select empno,ename,job,deptno from test1

minusselect empno,ename,job,deptno from test2;

EMPNO ENAME      JOB           DEPTNO

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

7782 CLARK      MANAGER           10

7839 KING       PRESIDENT         10

7934 MILLER     CLERK             10

吕星昊

2015.3.24