sql：检查表A中的条目是否存在于表B中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15938180/
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: check if entry in table A exists in table B
提问by codingknob
I have a definition table that I know is not being maintained very well, lets call this table A. I have another table (call it table B) that is much smaller and ideally should be a subset of table Abut I know that table Ais somewhat stale and does not contain new entries that are in Table B.
我有一个定义表，我知道它没有得到很好的维护，我们称之为table A. 我有另一个表（称之为table B），它要小得多，理想情况下应该是 asubset of table A但我知道它table A有点陈旧并且不包含Table B.
Note, that tables A and B have different columns.
请注意，表 A 和 B 具有不同的列。
Table A:
ID, Name, blah, blah, blah, blah
Table B:
ID, Name
I want all rows in Table B such that the ID in Table B does NOT exist in Table A. This does not just match rows in Table A; I want only rows in Table B where the ID does NOT exist at all in Table A.
我想要表 B 中的所有行，使得表 B 中的 ID 不存在于表 A 中。这不只是匹配表 A 中的行；我只想要表 B 中表 A 中根本不存在 ID 的行。
回答by gdoron is supporting Monica
SELECT *
FROM B
WHERE NOT EXISTS (SELECT 1
FROM A
WHERE A.ID = B.ID)
回答by Danylo Zherebetskyy
The classical answer that works in almost every environment is
适用于几乎所有环境的经典答案是
SELECT ID, Name, blah, blah
FROM TableB TB
LEFT JOIN TableA TA
ON TB.ID=TA.ID
WHERE TA.ID IS NULL
sometimes NOT EXISTS may be not implemented (not working).
有时 NOT EXISTS 可能没有实现（不工作）。
回答by Abe Miessler
If you are set on using EXISTS you can use the below in SQL Server:
如果您设置使用 EXISTS，则可以在 SQL Server 中使用以下内容：
SELECT * FROM TableB as b
WHERE NOT EXISTS
(
SELECT * FROM TableA as a
WHERE b.id = a.id
)
回答by Kai Dong
This also works
这也有效
SELECT *
FROM tableB
WHERE ID NOT IN (
SELECT ID FROM tableA
);
回答by Daflanck
Or if "NOT EXISTS" are not implemented
或者如果没有实现“NOT EXISTS”
SELECT *
FROM B
WHERE (SELECT count(*) FROM A WHERE A.ID = B.ID) < 1
