NotIn不等号对比,用exits代替in效果更好

Not in 在解析时会转化成一系列的 and <>

case 1

SELECT @results = count(filterCriterion_sv)
 FROM tbl_IN_VS_AND
 WHERE filterCriterion_sv NOT IN (214, 215, 216, 217)

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 )