EXISTS 代替 in ,not EXISTS 代替 not in

表 A ：ID ，Name

B：ID，B_Name

 

SELECT Name FROM A WHERE  A.ID NOT IN (SELECT ID FROM B)

改成

SELECT Name FROM A WHERE　not  EXISTS  (SELECT ID FROM B WHERE A.ID = B.ID)

 

 

SELECt * FROM HR_QustionNew..T_Class AS A
 where  not EXISTS (SELECT class_id FROM T_Class where T_Class.class_id = A.class_id)

 

 例：

SELECT * FROM T_Questionnaire where not exists(SELECT PS_ID FROM T_Personnel WHERE PS_ID = T_Questionnaire.user_ID)

 

 

例2      多条件处理

SELECT FLDM,CKDM INTO #Tmp2 FROM
(SELECT 'A' FLDM,'B' CKDM, 1 SL 
UNION ALL
SELECT 'A1','B1', 1
UNION ALL
SELECT 'A2','B2' , 1
UNION ALL
SELECT 'A3','B3', 1 ) AS  a

SELECT FLDM,CKDM INTO #Tmp3 FROM
(SELECT 'A' FLDM,'B'  CKDM
UNION ALL
SELECT 'A1','B1'
) AS  a

SELECT * FROM #Tmp2 H WHERE NOT EXISTS(SELECT FLDM,CKDM FROM #Tmp3 D WHERE D.FLDM =H.FLDM AND D.CKDM = H.CKDM )

 

结果