⼀天⼀篇mysql之⼋：mysql中notin和notexist选择
两者⽤法⽤法:
1、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。
2、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。、
具体可参考
两者性能分析：
如果查询语句使⽤了not in 那么内外表都进⾏全表扫描，没有⽤到索引；
⽽not extsts 的⼦查询依然能⽤到表上的索引。
所以⽆论那个表⼤，⽤not exists都⽐not in要快。
也就是说，in和exists需要具体情况具体分析，not in和not exists就不⽤分析了，尽量⽤not exists就好了。
典型的连接类型共有3种：
排序 - - 合并连接(Sort Merge Join (SMJ) )
嵌套循环(Nested Loops (NL) )
哈希连接(Hash Join)
嵌套循环和哈希连接的算法还是有不同，在理论上哈希连接要快过排序和nl，当然实际情况⽐理论上有复杂的多，不过两者还是有差异的.
1 关联⼦查询与⾮关联⼦查询
关联⼦查询需要在内部引⽤外部表，⽽⾮关联⼦查询不要引⽤外部表。对于⽗查询中处理的记录来说，⼀个关联⼦查询是每⾏计算⼀次，然⽽⼀个⾮关联⼦查询只会执⾏⼀次，⽽且结果集被保存在内存中（如果结果集⽐较⼩），或者放在⼀张oracle临时数据段中（如果结果集⽐较⼤）。⼀个“标量”⼦查询是⼀个⾮关联⼦查询，返回唯⼀记录。如果⼦查询仅仅返回⼀个记录，那么oracle优化器会将结果缩减为⼀个常量，⽽且这个⼦查询只会执⾏⼀次。
/select from emp where deptno in (select deptno from dept where dept_name=’admin’);*/
2.如何选择？
根据外部查询，以及⼦查询本⾝所返回的记录的数⽬。如果两种查询返回的结果是相同的，哪⼀个效率更好？
关联⼦查询的系统开销：对于返回到外层查询的记录来说，⼦查询会每次执⾏⼀次。因此，必须保证任何可能的时候⼦查询都要使⽤索引。
⾮关联⼦查询的系统开销：⼦查询只会执⾏⼀次，⽽且结果集通常是排好序的，并保存在临时数据段中，其中每⼀个记录在返回时都会被⽗级查询引⽤，在⼦查询返回⼤量记录的情况下，将这些结果集排序回增⼤系统的开销。
所以：如果⽗查询只返回较少的记录，那么再次执⾏⼦查询的开销不会⾮常⼤，如果返回很多数据⾏，那么直查询就会执⾏很多次。 如果⼦查询返回较少的记录，那么为内存中保存⽗查询的结果集的系统开销不会⾮常⼤，如果⼦查询返回多⾏，那么需要将结果放在临时段上，然后对数据段排序，以便为负查询中的每个记录服务。
3结论：
1）在使⽤⼀个关联⼦查询是，使⽤in 或者 exists⼦句的⼦查询执⾏计划通常都相同
2）exists⼦句通常不适于⼦查询
3）在外部查询返回相对较少记录时，关联⼦查询⽐⾮关联⼦查询执⾏得要更快。
4）如果⼦查询中只有少量的记录，则⾮关联⼦查询会⽐关联⼦查询执⾏得更快。
4 ⼦查询转化：⼦查询可以转化为标准连接操作
1）使⽤in的⾮关联⼦查询（⼦查询唯⼀）
exists的用法条件：1）在整个层次结构中最底层数据表上定义唯⼀主键的数据列存在于⼦查询的select列表中
2）⾄少有个定义了唯⼀主键的数据列在select列表中，⽽且定义唯⼀主键的其他数据列都必须有指定的相等标准，不管是直接指定，还是间接指定。
2）使⽤exists⼦句的关联⼦查询
条件：对于相关条件来说，该⼦查询只能返回⼀个记录。
5。not in和not exists调整
1）not in ⾮关联⼦查询：转化为in写法下的minus⼦句
2）not exists关联⼦查询：这种类型的反连接操作会为外部查询中每⼀个记录进⾏内部查询，除了不满⾜⼦查询中where条件的内部数据表以外，他会过滤掉所有记录。
可以重写：在⼀个等值连接中指定外部链接条件，然后添加select distinct
eg:select distinct ... from a,b l1 = b.col1(+) l1 is null

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。