in和not in
sql表
tab1
tab2
语法案例:
SELECT * FROM tab1 WHERE size IN (
SELECT size FROM tab2
)
SELECT * FROM tab1 WHERE size NOT IN (
SELECT size FROM tab2
)
如果参数有空值 null
SELECT * FROM tab1 WHERE size IN (
10,20,50,NULL
)
SELECT * FROM tab1 WHERE size NOT IN (
10,20,50,NULL
)
对于not in查询,内表存在空值将导致最终的查询结果为空
EXISTS 和 NOT EXISTS
EXISTS 用来判断查询到的结果是否为空,它并不返回任何值,
NOT EXISTS 也是一样
SELECT * FROM tab1 WHERE EXISTS(
SELECT size FROM tab2 WHERE tab1.size=tab2.size
)
SELECT * FROM tab1 WHERE NOT EXISTS(
SELECT size FROM tab2 WHERE tab1.size=tab2.size
)
对于not exists查询,内表存在空值对查询结果没有影响;
总结区别 :
1、对于not exists查询,内表存在空值对查询结果没有影响;对于not in查询,内表存在空值将导致最终的查询结果为空。
2、对于not exists查询,外表存在空值,存在空值的那条记录最终会输出;对于not in查询,外表存在空值,存在空值的那条记录最终将被过滤,其他数据不受影响。
in与not in,exists与not exists性能分析
in和exists
- in是把外表和内表作hash连接
- 而exists是对外表作loop循环,每次loop循环再对内表进行查询,关联子查询
例如:表A(小表),表B(大表)
select * from A where cc in(select cc from B) -->效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) -->效率高,用到了B表上cc列的索引。
相反的:
select * from B where cc in(select cc from A) -->效率高,用到了B表上cc列的索引
select * from B where exists(select cc from A where cc=B.cc) -->效率低,用到了A表上cc列的索引。
总结:
- 如果查询的两个表大小相当,那么用in和exists差别不大;
- 如果两个表中一个较小一个较大,则子查询表大(内表大)的用exists
- 如果两个表中一个较小一个较大,子查询表小(内表小)的用in;
not in 和not exists
原理:
- not in:做hash,对子查询表建立内存数组,用外表匹配,那子查询要是有NULL那外表没的匹配最终无值返回。
- not exists:做NL,对子查询先查,有个虚表,有确定值,所以就算子查询有NULL最终也有值返回
not in 和not exists 效率上对比:
-
如果查询语句使用了not in,那么对内外表都进行全表扫描,无法命中索引.
-
而not exists的子查询依然能用到表上的索引
所以无论哪个表大,用not exists都比not in 要快。