查询集合操作intersect与minus

上一篇博文中已经写到：intersect表示交集，minus表示差集。
---把查询hr用户employees表与Scott用户emp表的权限给suxing用户：

sys@PROD>grant select on hr.employees to suxing;

Grant succeeded.

 

sys@PROD>grant select on scott.emp to suxing;

Grant succeeded.

#授权成功。

 

---尝试查询Scott用户的表emp的记录：

suxing@PROD>col  EMPNO for 9999;

suxing@PROD>col MGR for 9999;

suxing@PROD>col DEPTNO for 99;

suxing@PROD>select * from scott.emp;

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

 

EMPNO ENAME      JOB         MGR HIREDATE         SAL       COMM DEPTNO

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

 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

 7777 SUSU       CLERK      7782 13-NOV-16       3000        500     20

15 rows selected.

 

---通过CAST方法从表emp记录中deptno=20,30的记录中创建表t1：

suxing@PROD>create table t1

  2  as select empno,ename,job,sal,deptno

  3  from scott.emp where deptno in (20,30);

Table created.

 

--查看表t1的表结构与记录：

suxing@PROD>desc t1;

 Name                                      Null?    Type

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

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 SAL                                                NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

 

suxing@PROD>select * from t1;

EMPNO ENAME      JOB              SAL DEPTNO

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

 7369 SMITH      CLERK            800     20

 7499 ALLEN      SALESMAN        1600     30

 7521 WARD       SALESMAN        1250     30

 7566 JONES      MANAGER         2975     20

 7654 MARTIN     SALESMAN        1250     30

 7698 BLAKE      MANAGER         2850     30

 7788 SCOTT      ANALYST         3000     20

 7844 TURNER     SALESMAN        1500     30

 7876 ADAMS      CLERK           1100     20

 7900 JAMES      CLERK            950     30

 7902 FORD       ANALYST         3000     20

EMPNO ENAME      JOB              SAL DEPTNO

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

 7777 SUSU       CLERK           3000     20

12 rows selected.

#返回共12条记录。

 

---通过CAST方法从表emp记录中deptno=20的记录中创建表t2：

suxing@PROD>create table t2

  2  as select empno,ename,job,sal,deptno

  3  from scott.emp where deptno = 20;

Table created.

#表已经创建。

--查看表t2表结构与记录：

suxing@PROD>desc t2;

 Name                                      Null?    Type

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

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 SAL                                                NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

suxing@PROD>select * from t2;

EMPNO ENAME      JOB              SAL DEPTNO

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

 7369 SMITH      CLERK            800     20

 7566 JONES      MANAGER         2975     20

 7788 SCOTT      ANALYST         3000     20

 7876 ADAMS      CLERK           1100     20

 7902 FORD       ANALYST         3000     20

 7777 SUSU       CLERK           3000     20

6 rows selected.

#返回共6条记录。

 

---进行intersect集合查询操作（交集部分）：

suxing@PROD>select * from t1

  2  intersect

  3  select * from t2;

EMPNO ENAME      JOB              SAL DEPTNO

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

 7369 SMITH      CLERK            800     20

 7566 JONES      MANAGER         2975     20

 7777 SUSU       CLERK           3000     20

 7788 SCOTT      ANALYST         3000     20

 7876 ADAMS      CLERK           1100     20

 7902 FORD       ANALYST         3000     20

6 rows selected.

#可以看到，intersect集合查询就是表t1,t2两表中重合部分，即deptno=20部分的记录。

 

---进行minus集合查询操作（差集）：

suxing@PROD>select * from t1

  2  minus

  3  select * from t2;

EMPNO ENAME      JOB              SAL DEPTNO

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

 7499 ALLEN      SALESMAN        1600     30

 7521 WARD       SALESMAN        1250     30

 7654 MARTIN     SALESMAN        1250     30

 7698 BLAKE      MANAGER         2850     30

 7844 TURNER     SALESMAN        1500     30

 7900 JAMES      CLERK            950     30

6 rows selected.

#返回6条记录，minus集合查询就是返回两表中相差的部分记录，级deptno=30的记录。

  

---将两个表互换位置进行minus集合查询操作（差集）：

suxing@PROD>select * from t2

  2   minus

  3  select * from t1;

no rows selected

#可以看到返回0条记录。

--可以看出，虽然两个表相差部分的记录是deptno=30部分的记录，
但是实质就是用前表的记录减去后表的记录相同部分得到的差集。所以进行minus集合查询的时候，
必须把记录多的表放在前面作为前表，把记录少的表放在后面作为后表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2128504/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2128504/