SQL 检查另一个表中是否不存在项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9366021/
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
Checking whether an item does not exist in another table
提问by user906153
My tables are set up something like this:
我的桌子是这样设置的：
table name: process
fields: name, id_string
table name: value_seach
fields: id_string, value
I want to construct a select statement that will display all of the process names (with it's respective id_string) that do not have an entry in value_search.
我想构造一个 select 语句，该语句将显示在 value_search 中没有条目的所有进程名称（带有各自的 id_string）。
The id_string in the process table can be null, and still have a name, but those need to be excluded if possible. The id_string in value_search can never be null
process 表中的 id_string 可以为null，并且仍然具有名称，但如果可能，需要排除那些。value_search 中的 id_string 永远不能为空
How do I do this?
我该怎么做呢？
回答by zgpmax
In general if you want rows that don't exist in another table, then LEFT JOIN the other table and WHERE ... IS NULL to a column on the second table. Also you mentioned that you don't want rows where process.id_string is NULL.
通常，如果您想要另一个表中不存在的行，则将 LEFT JOIN 另一个表和 WHERE ... IS NULL 连接到第二个表中的列。您还提到您不想要 process.id_string 为 NULL 的行。
SELECT p.name, p.id_string
FROM
process p
LEFT JOIN value_search v
ON v.id_string = p.id_string
WHERE
v.id_string IS NULL
AND p.id_string IS NOT NULL
This is known as an anti-join.
这称为反连接。
回答by Magnus
I believe using Not Existswould be your best option here.
我相信使用Not Exists将是您最好的选择。
SELECT p.name, p.id_string
FROM process p
WHERE
NOT p.id_string IS NULL AND
NOT EXISTS(
SELECT NULL
FROM value_search v
WHERE p.id_string = v.id_string)
回答by Brian Driscoll
The query you want should look something like this. Note that a JOIN will be significantly faster than a subquery in the WHERE clause.
您想要的查询应如下所示。请注意，JOIN 将比 WHERE 子句中的子查询快得多。
SELECT p.name, p.id_string
FROM process p
LEFT OUTER JOIN value_search v
ON p.id_string = v.id_string
AND p.id_string IS NOT NULL
AND v.id_string IS NULL
An equally valid variant of the query above would be:
上述查询的一个同样有效的变体是：
SELECT p.name, p.id_string
FROM process p
LEFT OUTER JOIN value_search v
ON p.id_string = v.id_string
WHERE
p.id_string IS NOT NULL
AND v.id_string IS NULL
回答by alexsuslin
SELECT
name,
id_string
FROM process
WHERE id_string IS NOT NULL AND id_string NOT IN SELECT id_string FROM value_seach
