集合运算符之全集、交集、补集【weber出品必属精品】

1. 集合的概念

   与数学中的全集、交集、补集的概念是一样的

2. 常用的集合运算符

   集合运算符的作用：把两个查询构造为一个联合查询

   1. 全集：求连个查询的全集

   union all：将两个查询的所有数据全部列出，不进行排序，不去掉重复的部分

   SQL> create table t1 as select * from emp where deptno in (10,20);
   
   Table created.
   
   SQL> create table t2 as select * from emp where deptno in (20,30);
   
   Table created.
   SQL> select * from t1;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7839 KING       PRESIDENT        17-NOV-81       5000            10
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20
         7934 MILLER     CLERK          7782 23-JAN-82       1300            10
   
   8 rows selected.
   
   SQL> select * from t2;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
         7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
         7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7900 JAMES      CLERK          7698 03-DEC-81        950            30
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20
   
   11 rows selected.
   
   SQL> select * from t1
     2  union all 
     3  select * from t2;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7839 KING       PRESIDENT        17-NOV-81       5000            10
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20
         7934 MILLER     CLERK          7782 23-JAN-82       1300            10
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
         7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
         7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7900 JAMES      CLERK          7698 03-DEC-81        950            30
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20
   
   19 rows selected.

   union：将两个查询的所有数据进行显示，但是重复的部分只显示一次，而且要按照第一个查询的第一列进行升序排序

   SQL> select * from t1
     2  union
     3  select * from t2;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
         7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
         7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
         7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7839 KING       PRESIDENT        17-NOV-81       5000            10
         7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7900 JAMES      CLERK          7698 03-DEC-81        950            30
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20
         7934 MILLER     CLERK          7782 23-JAN-82       1300            10
   
   14 rows selected.

   union all与union的性能哪个更高？

   union all性能更高：因为union all 不进行排序，也不去重
   
   2. 交集：INTERSECT

   SQL> select * from t1
     2  intersect
     3  select * from t2;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369 SMITH      CLERK          7902 17-DEC-80        800            20
         7566 JONES      MANAGER          7839 02-APR-81       2975            20
         7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
         7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
         7902 FORD       ANALYST          7566 03-DEC-81       3000            20

   3. 补集：MINUS
   查询select * from e2的补集：

   SQL> select * from t1
     2  minus
     3  select * from t2;
   
        EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
         7839 KING       PRESIDENT        17-NOV-81       5000            10
         7934 MILLER     CLERK          7782 23-JAN-82       1300            10

3. SET运算符规则

   1. 每个查询列表中的表达式的个数和数据类型必须相匹配

   select后面的列的个数必须要一致：

   select empno,ename,deptno,sal from e1
   union all
   select empno,ename,deptno from e2;
   
   select empno,ename,deptno,sal from e1
   *
   第 1 行出现错误:
   ORA-01789: 查询块具有不正确的结果列数
   
   select empno,ename,deptno,sal from e1
   union all
   select empno,ename,deptno,null from e2
   
   null是可以的
   数据类型必须一致：
   
   select empno,ename,deptno,sal from e1
   union all
   select empno,ename,deptno,'abc' from e2
   
   第 1 行出现错误:
   ORA-01790: 表达式必须具有与对应表达式相同的数据类型

   2. 可以使用括号来改变执行的顺序

   select * from e1
   intersect
   select * from e2
   union
   select * from e2;
   
   
   ENAME  EMPNO   SAL DEPTNO
   ------ ----- ----- ------
   ADAMS   7876  1100     20
   ALLEN   7499  1600     30
   BLAKE   7698  2850     30
   FORD    7902  3000     20
   JAMES   7900   950     30
   JONES   7566  2975     20
   MARTIN  7654  1250     30
   SCOTT   7788  4000     20
   SMITH   7369   800     20
   TURNER  7844  1500     30
   WARD    7521  1250     30
   
   已选择11行。
   
   
   select * from e1
   intersect
   (select * from e2
   union
   select * from e2);
   
   ENAME  EMPNO   SAL DEPTNO
   ------ ----- ----- ------
   ADAMS   7876  1100     20
   FORD    7902  3000     20
   JONES   7566  2975     20
   SCOTT   7788  4000     20
   SMITH   7369   800     20

   3. ORDER BY 子句的使用:

   除了union all之外，其他的集合运算符都要按照第一个查询的第一列，进行升序

   只可以在语句的最后出现：

   select empno,ename,sal,deptno from e2
   union
   select empno,ename,sal,deptno from e2
   order by ename desc;
   
   
   EMPNO ENAME    SAL DEPTNO
   ----- ------ ----- ------
    7521 WARD    1250     30
    7844 TURNER  1500     30
    7369 SMITH    800     20
    7788 SCOTT   4000     20
    7654 MARTIN  1250     30
    7566 JONES   2975     20
    7900 JAMES    950     30
    7902 FORD    3000     20
    7698 BLAKE   2850     30
    7499 ALLEN   1600     30
    7876 ADAMS   1100     20

   可以使用第一个查询语句的列名、别名、或位置（号）

   select empno,ename name1,sal,deptno from e2
   union
   select empno,ename name2,sal,deptno from e2
   order by name1 desc;
   
   
   EMPNO NAME1        SAL DEPTNO
   ----- ---------- ----- ------
    7521 WARD        1250     30
    7844 TURNER      1500     30
    7369 SMITH        800     20
    7788 SCOTT       4000     20
    7654 MARTIN      1250     30
    7566 JONES       2975     20
    7900 JAMES        950     30
    7902 FORD        3000     20
    7698 BLAKE       2850     30
    7499 ALLEN       1600     30
    7876 ADAMS       1100     20
   
   已选择11行。
   
   select empno,ename name1,sal,deptno from e2
   union
   select empno,ename name2,sal,deptno from e2
   order by name2 desc;
   
   order by name2 desc
            *
   第 4 行出现错误:
   ORA-00904: "NAME2": 标识符无效
   
   
   select empno,ename,sal,deptno from e2
   union
   select empno,to_char(sal) salary, null,deptno from e2
   order by 2;
   
   EMPNO ENAME    SAL DEPTNO
   ----- ------ ----- ------
    7876 1100             20
    7521 1250             30
    7654 1250             30
    7844 1500             30
    7499 1600             30
    7698 2850             30
    7566 2975             20
    7902 3000             20
    7788 4000             20
    7369 800              20
    7900 950              30
    7876 ADAMS   1100     20
    7499 ALLEN   1600     30
    7698 BLAKE   2850     30
    7902 FORD    3000     20
    7900 JAMES    950     30
    7566 JONES   2975     20
    7654 MARTIN  1250     30
    7788 SCOTT   4000     20
    7369 SMITH    800     20
    7844 TURNER  1500     30
    7521 WARD    1250     30

   第一个查询语句的列名出现在结果中

   select empno,to_char(sal) salary, null,deptno from e2
   union
   select empno,ename,sal,deptno from e2
   order by 2
   /
   
   
   EMPNO SALARY                                         NULL DEPTNO
   ----- ---------------------------------------- ---------- ------
    7876 1100                                                    20
    7521 1250                                                    30
    7654 1250                                                    30
    7844 1500                                                    30
    7499 1600                                                    30
    7698 2850                                                    30
    7566 2975                                                    20
    7902 3000                                                    20
    7788 4000                                                    20
    7369 800                                                     20
    7900 950                                                     30
    7876 ADAMS                                          1100     20
    7499 ALLEN                                          1600     30
    7698 BLAKE                                          2850     30
    7902 FORD                                           3000     20
    7900 JAMES                                           950     30
    7566 JONES                                          2975     20
    7654 MARTIN                                         1250     30
    7788 SCOTT                                          4000     20
    7369 SMITH                                           800     20
    7844 TURNER                                         1500     30
    7521 WARD                                           1250     30

   查询语句的匹配：个数和数据类型的匹配

   select empno,ename,sal,deptno from e1
   union
   select 1,to_char(sal),null,10 from e2;
   
   EMPNO ENAME    SAL DEPTNO
   ----- ------ ----- ------
       1 1100             10
       1 1250             10
       1 1500             10
       1 1600             10
       1 2850             10
       1 2975             10
       1 3000             10
       1 4000             10
       1 800              10
       1 950              10
    7369 SMITH    800     20
    7566 JONES   2975     20
    7782 CLARK   2450     10
    7788 SCOTT   4000     20
    7839 KING    5000     10
    7876 ADAMS   1100     20
    7902 FORD    3000     20
    7934 MILLER  1300     10