SQL Server - 不在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5748620/
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
SQL Server - NOT IN
提问by Madam Zu Zu
I need to build a query that will show me records that are in Table 1, but that are not in Table 2, based on the make-model-serial number combination.
我需要构建一个查询，该查询将根据 make-model-serial number 组合显示表 1 中但不在表 2 中的记录。
I know for fact that there are 4 records that differ, but my query always comes back blank.
我知道事实上有 4 条记录不同，但我的查询总是返回空白。
SELECT *
FROM Table1 WHERE MAKE+MODEL+[Serial Number] NOT IN
(SELECT make+model+[serial number] FROM Table2)
Table 1 has 5 records.
表 1 有 5 条记录。
When I change the query to IN, I get 1 record. What am I doing wrong with the NOT?
当我将查询更改为 时IN，我得到 1 条记录。我做错了NOT什么？
回答by Dave Markle
It's because of the way NOT IN works.
这是因为NOT IN 的工作方式。
To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS:
为了避免这些麻烦（以及在许多情况下更快的查询），我总是更喜欢 NOT EXISTS：
SELECT *
FROM Table1 t1
WHERE NOT EXISTS (
SELECT *
FROM Table2 t2
WHERE t1.MAKE = t2.MAKE
AND t1.MODEL = t2.MODEL
AND t1.[Serial Number] = t2.[serial number]);
回答by Joe Stefanelli
You're probably better off comparing the fields individually, rather than concatenating the strings.
您最好单独比较字段，而不是连接字符串。
SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.MAKE = t2.MAKE
AND t1.MODEL = t2.MODEL
AND t1.[serial number] = t2.[serial number]
WHERE t2.MAKE IS NULL
回答by Imtiaz
SELECT * FROM Table1
WHERE MAKE+MODEL+[Serial Number] not in
(select make+model+[serial number] from Table2
WHERE make+model+[serial number] IS NOT NULL)
That worked for me, where make+model+[serial number]was one field name
这对我有用，make+model+[serial number]一个字段名称在哪里
回答by Kris Ivanov
SELECT [T1].*
FROM [Table1] AS [T1]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [Table2] AS [T2]
WHERE ([T2].[MAKE] = [T1].[MAKE]) AND
([T2].[MODEL] = [T1].[MODEL]) AND
([T2].[Serial Number] = [T1].[Serial Number])
);
回答by Rebecca Chernoff
Use a LEFT JOIN checking the right side for nulls.
使用 LEFT JOIN 检查右侧是否有空值。
SELECT a.Id
FROM TableA a
LEFT JOIN TableB on a.Id = b.Id
WHERE b.Id IS NULL
The above would match up TableA and TableB based on the Id column in each, and then give you the rows where the B side is empty.
以上将根据每个中的 Id 列匹配 TableA 和 TableB，然后为您提供 B 侧为空的行。
回答by Shan Plourde
One issue could be that if either make, model, or [serial number] were null, values would never get returned. Because string concatenations with null values always result in null, and not in () with null will always return nothing. The remedy for this is to use an operator such as IsNull(make, '') + IsNull(Model, ''), etc.
一个问题可能是，如果 make、model 或 [serial number] 为空，则永远不会返回值。因为字符串与空值的连接总是导致空值，而不是在 () 与空值将始终不返回任何内容。对此的补救方法是使用诸如 IsNull(make, '') + IsNull(Model, '') 等运算符。
