Note3 : EXISTS、NOT EXISTS、IN、NOT IN使用

Note3 EXISTS、NOT EXISTS、IN、NOT IN使用

 1、子查询
        1.1 特征：
       子查询是指在一个select 语句中嵌套另一个select 语句，子查询必须包含括号，一个子查询会返回一个标量、一个行、一个列、一个表，子查询的限制是其外部语句必须是SELECT 、INSERT、UPDATE 、DELETE 、UPDATE、SET or DO
        1.2 分类：
       根据期望值的数量：标量子查询（返回一个值或NULL）、多值子查询。
       根据对外部的依赖性： 独立子查询、相关子查询
        PS：如果标量子查询返回多个值，mysql数据库会抛出错误。
       mysql数据库对IN 子查询优化时存在一个问题，对于IN 语句的优化是LAZY的，对于IN子句，如果不是显式的，那么IN子句都会转化为EXIST的相关子查询，性能比较慢。对于相关子查询，一般可以通过派生表来进行重写优化，避免相关子查询和外部查询的多次比较操作。
 2、EXISTS、NOT EXISTS、IN、NOT IN
       EXISTS，检查查询是否产生某些行，该谓词只返回TRUE或FALSE,L在EXISTS 子查询中 * 可以放心的使用（EXISTS 只关心行是否存在、而不会去取值）
       使用IN和EXISTS一样，对于IN，除了返回TRUE 或FALSE外，还会对NULL值返回UNKNOWN，但是过滤器中，UNKNOWN的处理方式和FALSE一样，SQL优化器会执行相同的计划。  
       但是NOT IN和NOT EXISTS 是存在差异的，这种差异表现在输入列表中包含了NULL
       输入列表中包含NULL值，IN会返回TRUE 或UNKNOWN (false 处理方式一样) ，
        NOT IN会返回FALSE或UNKNOWN， 而对于NOT EXISTS ，总是返回TRUE或 FALSE，这个就是他们的差别
     例子：  NULL IN （  'a','b' ,NULL） ----> IN 返回 UNKNOWN 
                 'c' NOT IN（  'a','b' ,NULL） ---->NOT IN返回 UNKNOWN
           
       所以如果输入列表中不含有NULL的时候，NOT IN 和 NOT EXIST的用法是一样的。
       在实际使用中，我们用NOT IN 查询时候，有可能会返回空集合（不是我们想要的结果），我们需要加一个过滤条件，过滤掉NULL
 如：select cust_no ,compay_id from customer cust where country = 'US' and cust_no not in (select cust_no from order.order where cust_no is not null )