Not In 不等號對比, 用exits代替in效果更好
來源：程序員人生 發布時間：2015-01-21 08:33:47 閱讀次數：6798次
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 )
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
