SQL 中的 NOT IN 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4197510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NOT IN condition in SQL
提问by user397316
Can anyone tell me the exact syntax for NOT IN condition in SQL on two columns.
谁能告诉我两列 SQL 中 NOT IN 条件的确切语法。
This is my query written in VBA.
这是我用 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.....
但这不起作用......
回答by Daniel Renshaw
You can't use INwith more than one column but you can usually achieve the same effect using EXISTS:
您不能使用IN多于一列，但通常可以使用EXISTS以下方法达到相同的效果：
SELECT *
FROM tbl1
WHERE NOT EXISTS
(
SELECT *
FROM tbl2
WHERE tbl2.col1 = tbl1.col1
AND tbl2.col2 = tbl1.col2
)
回答by Hogan
General syntax:
一般语法：
where col not in (items)
Items can be
物品可以
a list of items --
(4,5,3,5,2)or('243','3','cdds')or any other datatype.Or a select statement
(select hatefulthings from table)
项目清单-
(4,5,3,5,2)或('243','3','cdds')或任何其它数据类型。或者选择语句
(select hatefulthings from table)
Addition 6 years later
6年后的加入
All major platforms support tuples with NOT IN, for example
例如，所有主要平台都支持带有 NOT IN 的元组
SELECT *
FROM empoyee
WHERE (empID, @date) NOT IN
(SELECT empID, vacationDay
FROM empVacation
)
In this example we select everything from the employee table where the tuple of employee id and date are not in a table containing vacation days.
在此示例中，我们从员工表中选择所有员工 ID 和日期元组不在包含假期的表中的所有内容。
回答by JosephStyons
Your question is a bit unclear. Is this what you need?
你的问题有点不清楚。这是你需要的吗？
SELECT *
FROM
MY_TABLE MT
WHERE 'Smith' NOT IN (MT.FIRST_NAME)
AND 'Smith' NOT IN (MT.LAST_NAME)
This will show you all records where the search phrase ("Smith") is in neither the first_name nor the last_name column.
这将向您显示搜索短语（“Smith”）既不在 first_name 列也不在 last_name 列中的所有记录。
回答by Bob Jarvis - Reinstate Monica
Perhaps you meant
也许你的意思是
SELECT *
FROM MY_TABLE
WHERE (FIRST_NAME, LAST_NAME) NOT IN (SELECT FIRST_NAME, LAST_NAME
FROM SOME_OTHER_TABLE)
This is allowed under Oracle - not sure about SQL Server.
这在 Oracle 下是允许的 - 不确定 SQL Server。
Share and enjoy.
分享和享受。
回答by JeffO
Select * from some_table
where (((Some_Value) Not IN (Select Column1 & Column2 from Some_Other_Table)));
I saw where you indicated this was for Access
我看到您指出这是用于 Access 的
回答by PatrickP61
It is not clear what you are asking, but I gather that you would like to have a NOT IN condition based on two columns instead of just one.
目前尚不清楚您在问什么，但我认为您希望基于两列而不是仅一列的 NOT IN 条件。
As an example, lets say you have a column called F_Name and another called L_Name (both variable in size), and that you want to exclude specific combination of those names from another table that has them already combined as NAME. In that case, you could do this:
例如，假设您有一个名为 F_Name 的列和另一个名为 L_Name 的列（两者的大小都是可变的），并且您希望从另一个已将它们组合为 NAME 的表中排除这些名称的特定组合。在这种情况下，你可以这样做：
select F_name
, L_name
, col1
, coln
from mytable1
where F_name -- First name (variable length)
|| ' ' -- appended to a blank space
|| L_name -- appended to the last name (v)
not in -- is not one of these names
( select name
from mytable2
where ...
)
The main issue with this query is that you must get the formatting just right so that they can match up exactly.
此查询的主要问题是您必须使格式恰到好处，以便它们可以完全匹配。
If you are dealing with a combination of fields with different types, like numeric and timestamps, then use any of the conversion commands (DECIMAL, INTEGER, CHAR, SUBSTR ...) at your disposal to convert to text equal and then match it up accordingly.
如果您正在处理不同类型的字段组合，例如数字和时间戳，则可以使用任意转换命令（DECIMAL、INTEGER、CHAR、SUBSTR ...）将其转换为相等的文本，然后将其匹配因此。
