Oracle 取两个表中数据的交集

 关键字: Oracle 取两个表中数据的交集 INTERSECT

Oracle 作为一个大型的关系数据库，日常应用中往往需要提取两个表的交集数据

例如现有如下表，要求找出工资2500(不含2500)以上并且是男性(M)的员工编号，那么就要利用这两个表的关系做一个交集了

 

employee

CODE	NAME	GENDER
001	Tom	M
002	Jerry	M
003	Ana	F

 

 

salary

CODE	SALARY
001	2800
002	2500
003	3000

 

方法1：利用操作符INTERSECT

INTERSECT 操作符用来合并两个查询，返回两个查询中都存在的记录，即返回两个查询结果的交集，前提是两个查询的列的数量和数据类型必须完全相同。

SELECT CODE FROM EMPLOYEE WHERE GENDER = 'M'
INTERSECT
SELECT CODE FROM SALARY WHERE SALARY > 2500

第一条SQL的结果为

CODE
----------
001
002

第二条SQL的结果为

CODE
----------
001
003

INTERSECT的结果

CODE
----------
001

方法2：利用关系SQL

SELECT A.CODE FROM EMPLOYEE A, SALARY B
WHERE A.CODE = B.CODE AND A.GENDER = 'M' AND B.SALARY > 2500

结果为

CODE
----------
001

以上两种方法是本人比较常用的方法，尤其第二种，充分体现关系数据的精髓所在。但如果两个表结构比较复杂，而且关系条件也比较难实现的话，第一种显然能够简单地得到需要的结果，也不失为一种好方法。

 

如果用exists的话还是用第一种。

 

 

 

先看下面的例子:oracle中两个系统表.emp,dept.

example:

1:not exists(not in)

 not exists:

   这条语句返回select * from scott.dept d where e.deptno=d.deptno and d.deptno=10条件满足的结果集.也就是说,

返回的结果集中不存在d.deptno=10结果集的记录,即emp表中没有dept表中d.deptno=10的记录.

 SQL> select empno,ename,deptno from scott.emp e where not exists(select * from scott.dept d where e.deptno=d.deptno and d.deptno=10);

EMPNO ENAME      DEPTNO
----- ---------- ------
 7369 SMITH          20
 7499 ALLEN          30
 7521 WARD           30
 7566 JONES          20
 7654 MARTIN         30
 7698 BLAKE          30
 7788 SCOTT          20
 7844 TURNER         30
 7876 ADAMS          20
 7900 JAMES          30
 7902 FORD           20

11 rows selected

not in:

第一个where条件必须给定字段(deptno), 第二个sql语句中,必须明确给定要查询的字段是哪个(deptno).

SQL> select empno,ename,deptno from scott.emp e where deptno not in(select deptno from scott.dept d where e.deptno=d.deptno and d.deptno=10);

EMPNO ENAME      DEPTNO
----- ---------- ------
 7369 SMITH          20
 7499 ALLEN          30
 7521 WARD           30
 7566 JONES          20
 7654 MARTIN         30
 7698 BLAKE          30
 7788 SCOTT          20
 7844 TURNER         30
 7876 ADAMS          20
 7900 JAMES          30
 7902 FORD           20

11 rows selected

2: exists(in)

  exists:

   这条语句返回select * from scott.dept d where e.deptno=d.deptno and d.deptno=10条件满足的记录结果集.

也就是说返回的结果集中只存在有d.deptno=10的记录,即emp表中只存在dept表中d.deptno=10的记录.

SQL> select empno,ename,deptno from scott.emp e where  exists(select * from scott.dept d where e.deptno=d.deptno and d.deptno=10);

EMPNO ENAME      DEPTNO
----- ---------- ------
 7782 CLARK          10
 7839 KING           10
 7934 MILLER         10

in:

第一个where条件必须给定字段(deptno), 第二个sql语句中,必须明确给定要查询的字段是哪个(deptno).

SQL> select empno,ename,deptno from scott.emp e where  deptno in(select deptno from scott.dept d where e.deptno=d.deptno and d.deptno=10);

EMPNO ENAME      DEPTNO
----- ---------- ------
 7782 CLARK          10
 7839 KING           10
 7934 MILLER         10

 

  oracle 中exists (in)和not exists(not in)是判断是否存在和不存在表中记录的关键子.

 请注意:not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。