sql cookbook 第三章 操作多个表 记

3.4 从一个表中查找另一个没有值

问题：要从表dept中查找在表emp中不存在数据的所有部门

1.没有deptno为null时：

select deptno from dept where deptno  not in (select deptno from emp);

2.当emp表中有deptno为null时：

错的：

select  deptno from dept where deptno  not in (select deptno from emp); 

结果： null    

原因：in 和not in 本质是or运算

select  false or null;

   null

select  true  or null; 

  1

注意：or运算中 ture or null ture  false or null null

解决：

解决与not in 和null有关的问题，可以使用 not exists和相关子查询。

select deptno from dept where not exists (select null from emp where emp.deptno=dept.deptno);  

等价

select d.deptno from dept d where not exists (select 1 from new_dept e where d.deptno=e.deptno);

 

3.7 检测两个表中是否有相同的数据

问题：知道两个表或视图中是否有相同的数据(基数和值)

create view v37
as 
    select * from emp where deptno !=10
        union all
    select * from emp where ename = 'WARD' ;

select * from v37;

select * from
(SELECT e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal,count(*) cnt from emp e group by  e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal)e
where not exists
	(select null from 
		(select v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal,count(*) cnt from v37 v group by v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal )v
		where e.comm=v.comm and e.deptno=v.deptno and e.empno=v.empno and e.ename=v.ename and e.hiredate=v.hiredate and e.job=v.job and e.mgr=v.mgr and e.sal=v.sal and v.cnt=e.cnt
	)
union ALL
select * from
(select v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal,count(*) cnt from v37 v group by  v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal)v
where not exists
	(select null from 
		(select e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal,count(*) cnt from emp e group by e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal )e
		where e.comm=v.comm and e.deptno=v.deptno and e.empno=v.empno and e.ename=v.ename and e.hiredate=v.hiredate and e.job=v.job and e.mgr=v.mgr and e.sal=v.sal and v.cnt=e.cnt
);

 在视图v中不在表emp中：

SELECT * from
(SELECT v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal,count(*) cnt from v37 v group by  v.comm,v.deptno,v.empno,v.ename,v.hiredate,v.job,v.mgr,v.sal)v
where not EXISTS
	(SELECT null from 
		(SELECT e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal,count(*) cnt from emp e group by e.comm,e.deptno,e.empno,e.ename,e.hiredate,e.job,e.mgr,e.sal )e
		where e.comm=v.comm and e.deptno=v.deptno and e.empno=v.empno and e.ename=v.ename and e.hiredate=v.hiredate and e.job=v.job and e.mgr=v.mgr and e.sal=v.sal and v.cnt=e.cnt
);

v37中有2条数据ename='ward'的数据，而emp只有一条。通过count(*) cnt 选出来了，多个那条v37的数据。