SQL中的NOT IN条件

本文介绍了SQL中的NOT IN条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

谁能在两列中告诉我SQL中NOT IN条件的确切语法.

Can anyone tell me the exact syntax for NOT IN condition in SQL on two columns.

这是我用VBA编写的查询.

This is my query written in VBA.

strNewSql = "SELECT distinct(tblRevRelLog_Detail.PartNumber), tblRevRelLog_Detail.ChangeLevel, tblRevRelLog_Detail.ID FROM tblRevRelLog_Detail LEFT JOIN tblEventLog ON tblRevRelLog_Detail.PartNumber = tblEventLog.PartNumber" strNewSql = strNewSql & " WHERE (tblEventLog.PartNumber) Not In(SELECT tblEventLog.PartNumber FROM tblEventLog WHERE tblEventLog.EventTypeSelected = 'pn REMOVED From Wrapper') AND tblEventLog.TrackingNumber = """ & tempTrackingNumber & """ AND tblEventLog.TrackingNumber = tblRevRelLog_Detail.RevRelTrackingNumber;"

我想更改此子查询，它应应用于两列的组合，如下所示:

I want to change this sub query like, it should apply on the combination of two columns as follows:

strNewSql = "SELECT tblRevRelLog_Detail.PartNumber, tblRevRelLog_Detail.ChangeLevel, tblRevRelLog_Detail.ID FROM tblRevRelLog_Detail LEFT JOIN tblEventLog ON tblRevRelLog_Detail.PartNumber = tblEventLog.PartNumber" strNewSql = strNewSql & " WHERE (((tblEventLog.PartNumber, tblEventLog.PartNumberChgLvl) Not In(SELECT tblEventLog.PartNumber,tblEventLog.PartNumberChgLvl FROM tblEventLog WHERE tblEventLog.EventTypeSelected = 'pn REMOVED From Wrapper') AND tblEventLog.TrackingNumber = """ & tempTrackingNumber & """ AND tblEventLog.TrackingNumber = tblRevRelLog_Detail.RevRelTrackingNumber);"

但这不起作用.....

But this is not working.....

推荐答案

IN不能用于多于一列，但通常可以使用EXISTS达到相同的效果:

You can't use IN with more than one column but you can usually achieve the same effect using EXISTS:

SELECT * FROM tbl1 WHERE NOT EXISTS ( SELECT * FROM tbl2 WHERE tbl2.col1 = tbl1.col1 AND tbl2.col2 = tbl1.col2 )