检查一个项目是否不存在于另一个表中

Question

我的表格设置大致如下：

table name: process
fields: name, id_string

table name: value_seach
fields: id_string, value

我想构造一个SELECT语句，以显示所有在value_search中没有条目的进程名称（及其相应的id_string）。

进程表中的id_string可以为null，并且仍然具有名称，但如果可能，这些需要被排除在外。value_search中的id_string永远不会为null。

我该如何做到这一点？

- user906153

---

你需要的关系运算符是半差集，又称为反向连接。 - onedaywhen

Answer 1

一般来说，如果你想要那些在另一个表中不存在的行，那么就使用LEFT JOIN连接另一个表，并WHERE ... IS NULL到第二个表的某一列。另外，您提到不想要process.id_string为空的行。

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

这被称为反连接（anti-join）。

- zgpmax

---

这非常有帮助。谢谢。 - 1man

请注意，如果您需要过滤要查找缺少连接的列（例如您的示例中的v），则需要在JOIN子句中而不是WHERE子句中执行此操作。这对于有更多SQL经验的人可能很明显，但这不是我尝试的第一件事，因此我认为它可能会帮助到其他人。 - Ibrahim

Answer 2

我认为在这里使用 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)

- Magnus

---

@BrianDriscoll 不，即使左表具有空值或没有空值，使用null检查和Not Exists的Left outer join将创建相同的查询计划。更多信息请参见：http://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/。如果值保证为“not null”，则Left join、Not exists和Not in将创建相同的查询计划。 - Magnus

请注意，对于许多其他的数据库管理系统（DBMS），例如SQL Server，使用带有NULL检查的“左外连接”将导致最差的性能表现，因为它无法区分反向连接（ANTI JOIN）。 - Magnus

一个查询，如果您想在not exists的where子句中检查第二个ID，它也可以工作。 - mashtheweb

这是在真实数据上进行测试的结果（PostgreSQL 9.4）：1）“左连接”：解释：“t1上的哈希右连接，过滤器t2.id为空，t2的序列扫描，t1的哈希，t1的序列扫描”；实际执行时间：11.9秒。2）“where not exists”：解释：“哈希反连接，t1的序列扫描，t2的哈希，t2的序列扫描”。实际执行时间：11.9秒。结论：实际执行时间相同，但第二种方法的查询计划看起来更好。 - bartolo-otrit

Answer 3

您需要的查询应该类似于这样。请注意，在WHERE子句中使用JOIN比子查询要快得多。

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

上述查询的另一种有效变体可能是：

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

- Brian Driscoll

Answer 4

SELECT 
  name,
  id_string
FROM process
WHERE id_string IS NOT NULL AND id_string NOT IN SELECT id_string FROM value_seach

- alexsuslin