sql函数--11--Not IN 或者 NOT EXISTS_sql not in

in和not in

sql表

tab1

tab2

语法案例:

SELECT *  FROM  tab1 WHERE size IN (
SELECT size FROM tab2
)
1
2
3

SELECT *  FROM  tab1 WHERE size NOT IN (
SELECT size FROM tab2
)
1
2
3

如果参数有空值 null

SELECT *  FROM  tab1 WHERE size  IN (
10,20,50,NULL
)
1
2
3

SELECT *  FROM  tab1 WHERE size NOT IN (
10,20,50,NULL
)
1
2
3

对于not in查询，内表存在空值将导致最终的查询结果为空

EXISTS 和 NOT EXISTS

EXISTS 用来判断查询到的结果是否为空，它并不返回任何值，

NOT EXISTS 也是一样

SELECT *  FROM  tab1 WHERE EXISTS(
SELECT size FROM tab2 WHERE tab1.size=tab2.size
)

1
2
3
4

SELECT *  FROM  tab1 WHERE NOT EXISTS(
SELECT size FROM tab2 WHERE tab1.size=tab2.size
)
1
2
3

对于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列的索引。

总结:

1. 如果查询的两个表大小相当，那么用in和exists差别不大；
2. 如果两个表中一个较小一个较大，则子查询表大(内表大)的用exists
3. 如果两个表中一个较小一个较大，子查询表小(内表小)的用in；

not in 和not exists

原理:

1. not in:做hash，对子查询表建立内存数组，用外表匹配，那子查询要是有NULL那外表没的匹配最终无值返回。
2. not exists:做NL，对子查询先查，有个虚表，有确定值，所以就算子查询有NULL最终也有值返回

not in 和not exists 效率上对比:

1. 如果查询语句使用了not in，那么对内外表都进行全表扫描,无法命中索引.

2. 而not exists的子查询依然能用到表上的索引

所以无论哪个表大，用not exists都比not in 要快。