Not In 不等號對比, 用exits代替in效果更好，不等號exits

Not In 不等號對比, 用exits代替in效果更好，不等號exits

Not in 在解析時會轉化成一系列的 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 )