Oracle集合运算

来源：互联网发布：机房网络配线架编辑：程序博客网时间：2024/05/18 20:09

集合运算：组合两个或多个部分查询的结果到一个结果中。包含集合运算的查询称为复合查询。

并集：UNION(联合)

SQL> select * from emp where deptno=10  2  union  3  select * from emp where deptno=20;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7369 SMITH                CLERK                    7902 17-12月-80            800                 20      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20      7839 KING                 PRESIDENT                     17-11月-81           5000                 10      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择8行。

并集（相交部分重复显示）

SQL> select * from emp where deptno=10 or deptno=20  2  union  3  select * from emp where deptno=10 or deptno=20;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7369 SMITH                CLERK                    7902 17-12月-80            800                 20      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20      7839 KING                 PRESIDENT                     17-11月-81           5000                 10      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择8行。SQL> select * from emp where deptno=10 or deptno=20  2  union all  3  select * from emp where deptno=10 or deptno=20;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7369 SMITH                CLERK                    7902 17-12月-80            800                 20      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20      7839 KING                 PRESIDENT                     17-11月-81           5000                 10      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10      7369 SMITH                CLERK                    7902 17-12月-80            800                 20      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20      7839 KING                 PRESIDENT                     17-11月-81           5000                 10      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择16行。

交集：INTERSECT(交叉)

SQL> select * from emp where sal>1000  2  intersect  3  select * from emp where sal>800;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20      7839 KING                 PRESIDENT                     17-11月-81           5000                 10      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择12行。

差集：MINUS (相减)

SQL> select * from emp where sal<1300  2  minus  3  select * from emp where sal>1000;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------      7369 SMITH                CLERK                    7902 17-12月-80            800                 20      7900 JAMES                CLERK                    7698 03-12月-81            950                 30

阅读全文

0 0