create or replace view v1 as select ename,job,sal from emp where deptno = 30;
create or replace view v2 as select ename,job,sal from emp;
-- 查找两个表中匹配的行
/*使用内连接*/
select v1.* from v1,v2 where v1.ename=v2.ename and v1.job=v2.job and v1.sal=v2.sal
/*使用半连接*/
select * from v1 where (ename,job,sal) in (select ename,job,sal from v2)
/*使用intersect函数*/
SQL> select * from v2
2  intersect
3  select * from v1;
ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
BLAKE      MANAGER         2850
JAMES      CLERK            950
MARTIN     SALESMAN        1250
TURNER     SALESMAN        1500
WARD       SALESMAN        1250
6 rows selected.
-- 查找两个表中不匹配的行
/*使用半连接，使用not in需要注意null值的问题*/
SQL> select * from v2 where (ename,job,sal) not in (select ename,job,sal from v1);
ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
JONES      MANAGER         2975
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
ADAMS      CLERK           1100
FORD       ANALYST         3000
MILLER     CLERK           1300
8 rows selected.
/* 使用minus函数, 注意表的先后顺序 */
SQL> select * from v2 minus select * from v1;
ENAME      JOB              SAL
---------- --------- ----------
ADAMS      CLERK           1100
CLARK      MANAGER         2450
FORD       ANALYST         3000
JONES      MANAGER         2975
KING       PRESIDENT       5000
MILLER     CLERK           1300
SCOTT      ANALYST         3000
SMITH      CLERK            800
8 rows selected.
/* 使用not in 的空值处理 */
create table emp1 (deptno number);
insert into emp1 values (10);
insert into emp1 values (20);
insert into emp1 values (50);
insert into emp1 values (null);
/* 下面查询返回结果为空 */
SQL> select deptno from dept where deptno not in (select deptno from emp1);
no rows selected
/* 解决方法一：添加is not null 条件 */
SQL> select deptno from dept where deptno not in (select deptno from emp1 where deptno is not null);
DEPTNO
----------
30
40
/* 解决方法二：使用相关连子查询,下面两个语句可以使用左外连接改写，见最后 */
SQL> select deptno from dept where not exists (select null from emp1 where dept.deptno=emp1.deptno);
DEPTNO
----------
30
40
SQL> select deptno from dept where deptno not in (select deptno from emp1 where emp1.deptno=dept.deptno);
DEPTNO
----------
30
40
/* 解决方法三：在字段上添加非空约束,这个需要根据实际业务需求处理 */
/* 返回dept在emp表中不匹配的记录，要求返回部门的所有信息 */
SQL> select * from dept where deptno not in (select deptno from emp where deptno is not null);
DEPTNO DNAME          LOC
---------- -------------- -------------
40 OPERATIONS     BOSTON
/* 上面的反连接可以使用做外连接改写 */
SQL> select dept.* from dept left join emp on dept.deptno=emp.deptno where emp.deptno is null;
DEPTNO DNAME          LOC
---------- -------------- -------------
40 OPERATIONS     BOSTON
SQL>