Not In 不等号对比, 用exits代替in效果更好
来源：程序员人生 发布时间：2015-01-21 08:33:47 阅读次数：6527次
Not in 在解析时会转化成1系列的 and <>
case 1
SELECT @results = count(filterCriterion_sv)
FROM tbl_IN_VS_AND
WHERE filterCriterion_sv NOT IN (214, 215, 216, 217)
case 2
SELECT @results = count(filterCriterion_sv)
FROM tbl_IN_VS_AND
WHERE filterCriterion_sv <> 214
AND filterCriterion_sv <> 215
AND filterCriterion_sv <> 216
AND filterCriterion_sv <> 217
case 3
SELECT @results = count(filterCriterion_sv)
FROM tbl_IN_VS_AND
LEFT OUTER JOIN (
SELECT 214 AS filterValue_val UNION
SELECT 215 UNION
SELECT 216 UNION
SELECT 217 ) AS tbl
ON tbl_IN_VS_AND.filterCriterion_sv = tbl.filterValue_val
WHERE tbl.filterValue_val IS NULL
case 4
SELECT @results = count(filterCriterion_sv)
FROM tbl_IN_VS_AND
WHERE NOT EXISTS(SELECT * FROM
(
SELECT 214 AS filterValue_val UNION ALL
SELECT 215 UNION ALL
SELECT 216 UNION ALL
SELECT 217 ) AS tbl
WHERE tbl.filterValue_val = tbl_IN_VS_AND.filterCriterion_sv )
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
