专题描述
详细说明not in 和not exist的区别，分析SQL语句中not in子句不能表达原意的问题。
问题提出
存储过程名称d_process_district_prime发现游标定义，原SQL语句：
DECLARE cur_city_code cursor
FOR
SELECT city_code
FROM (
SELECT t.city_code from wom_tb_district t where t.city_code != '' and t.city_code is not null group by t.city_code
UNION
SELECT t.province_code as city_code from wom_tb_district t where t.province_code != '' and t.province_code is not null group by t.province_code
UNION
SELECT t.country_code as city_code from wom_tb_district t where t.country_code != '' and t.country_code is not null group by t.country_code
) xt
WHERE xt.city_code NOT IN (SELECT p.city_code FROM tm_prime p where AND p.city_code != '' AND p.city_code IS NULL);
其中WHERE xt.city_code NOT IN (SELECT p.city_code FROM tm_prime p where AND p.city_code != '' AND p.city_code IS NULL); 子句语义不合理
分析过程
原SQL语句中SELECT city_code ......UNION ......UNION ......) xt可以返回不为空的查询结果集，而p.city_code != '' AND p.city_code IS NULL只能返回NULL结果集 前后语义相矛盾，经过讨论实际研发人员SQL语句时所表达含义为查询xt结果集中不符合NOT IN子句中判断条件的结果集。
研发人员原意：途中×××部分，原语句中表达结果集可能为空。
问题原因
SQL语句与实际表达含义不相同。
解决方案
1
修改原语句语义将原SQL语句修改为：
SELECT city_code ......UNION ......UNION ......) xt
WHERE NOT IN (SELECT 1 FROM tm_prime p WHERE xt.city_code=p.city_code AND p.city_code != '' AND p.city_code IS NOT NULL);
2
修改原语句运行效率将原SQL语句修改为：
SELECT city_code ......UNION ......UNION ......) xt
WHERE NOT EXISTS (SELECT 1 FROM tm_prime p WHERE xt.city_code=p.city_code AND p.city_code != '' AND p.city_code IS NOT NULL);
3
添加索引提高查询效率：
ALTER TABLE wom_tb_district ADD INDEX idx_ccode(country_code);
ALTER TABLE wom_tb_district ADD INDEX idx_pcode(province_code);
ALTER TABLE wom_tb_district ADD INDEX idx_ccode(city_code);
ALTER TABLE tm_prime ADD INDEX idx_ccode(city_code);
知识点
1
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。
2
IN子句用于判断外查询结果是否在子句查询结果集中。
转播
