SQLCookBook第三章学习日记6

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

问题：要从一个表是（称之为源表）中查找在另一目标表中不存在的值。例如，要从表中dept中查找在表emp中不存在数据的所有部门。在示例数据中，dptno值为40的记录在表emp中不存在。

解决方案：求差集函数对解决这个问题非常有用。DB2、PostgreSQL和Oracle支持差集操作。如果DBMS不支持差集函数，可以像MySQL和SQL Server 方案一样使用子查询。

DB2 和PostgreSQL
使用集合操作except

select deptno from dept    except select deptno from emp

Oracle
使用集合操作minus;

select deptno from     minus select deptno from emp

MySQL和SQL Server
使用子查询返回表emp中所有的deptno，而外层查询则从dept表中查找子查询的结果中所没有的行

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

讨论：
DB2和PostgreSQL
用DB2和PostgreSQL提供的内置函数可以很容易的完后这种操作。except操作符获取第一个结果集，并从中去掉那些第二个结果集中也有的行。这种操作与减法十分相似。

except的使用也有一定的限制，两个select列表中的值得数目和数据类型必须匹配，此外，except不会返回重复行，而且跟使用 not in的子查询不同，NULL值不会有问题。except操作符将从前一个查询（except之前的查询）的结果中返回所有在后一个查询结果中所没有的行。

Oracle
Oracle 与DB2和PostgreSQL的解决方案一样，只是在Oracle中差集的操作符为minus，而不是except，除此之外，前面的解释也同样适用于Oracle

* MySQL和SQL Server*
在子查询中，将返回表emp中的所有deptno值，而外层查询将返回dept表中满足如下条件的deptno值：“不在”或“不包含在”子查询结果集中。

当使用MySQL和SQL Server 解决方案时，应当考虑如何删除重复行。其他平台基于except和minus的解决方案从结果集中去除了重复行。从而确保每个dptno值在结果中只出现一次。

当然，本例是不会有重复值的，因为在示例数据库中deptno是关键字字段。如果deptno不是关键字，可以使用distinct子句来确保每个在emp表中没有的deptno值只在结果中出现一次。

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

当使用not in子句时，一定要注意null值的问题。考虑下年的new_dept；

create table new_dept (deptno interger)    insert into new_dept values(10)    insert into new_dept values(50)    insert into new_dept values(null)

如果使用子查询和not in子句时，来查找表dept中所有在new_dept表中没有的deptno值，将会发现返回的查询结果是空的。

select * from dept    where deptno not in (select deptno from new_dept)

deptno值为20、30、40的记录在表new_dept并不存在，但这个查询没有返回结果。原因就是在表new_dept中有一个空值。子查询返回的三行结果集中，deptno的值分别为10、50和NULL。IN和NOT IN本质上是or运算，因而计算逻辑or时处理NULL的方式不同、产生的结果也不同。考虑下面列出的几个例子，其中分别使用了in与其等价的or操作符：

select deptno from dept    where deptno in (10,50,null)

select deptno from dept     where(deptno=10 or deptno=null)

现在考虑同样的例子，分别使用了 not in 和 not or操作符：

select deptno from dept    where deptno not in (10,50,null)select deptno from dept    where not (deptno=10 or deptno=50 or deptno=null)

可以看到，条件deptno not in （10，50，null）等同于：

not (deptno=10 or deptno=50 or deptno=null)    (false or false or null)    (false or null)    null

在SQL中，TRUE orNULL 的结果就是TRUE ，FLASE or NULL的结果是NULL！当在结果中有一个NULL值时，NULL就会延续下去（除非采用了跟1.1节中类似的技巧对NULL值作特别测试）。在使用in谓词以及进行逻辑or计算，并且与NULL值相关的情况下，这一点必须要记住。
要解决与not in 和null相关的问题，可以使用notexists和相关子查询。这里用了“相关子查询”这一术语，因为子查询中要引用外部查询的行。下面的解决方案不受空值的影响：

select d.deptno from dept d    where not exists(        select null from e             where d.deptno = e.deptno        )

从理论上来讲，在这种解决方案的外层查询中，考虑到了表dept中的每一行。对于dept表中的每一行，有以下几种情况。

1.执行子查询，查找部门编号是否在表emp中，注意条件d.deptno= e.deptno,该条件将两个表的部门编号合并到一起。2.如果子查询返回结果，那么，exists（）的值为真，not exists(...)的结果为false，那么，从外层查询中得到的该行将被返回（因为该部门并没有在emp表中）3.exists/not exists 与相关子查询一起使用的时候，子查询select列表中的项目并不重要，因此，这里选择了NULL，把重点放在联接子查询中么不是select列表的项目。