SQL查询以查找在另一个表中不存在的记录的ID。

Question

我在数据库中有两个具有绑定主键的表，我想找到它们之间的不相交集合。例如，

Table1

ID Name 1 John 2 Peter 3 Mary

Table2

ID Address 1 address2 2 address2

那么，我该如何创建一个SQL查询，以便从table1中获取在table2中不存在的ID行？在这种情况下，应返回(3, Mary)。

PS: ID是这两个表的主键。

- johnklee

---

作为未来提问的提示：始终定义您正在使用的数据库系统（以及该数据库的哪个版本）。SQL只是大多数数据库系统使用的结构化查询语言 - 这并没有真正提供太多帮助...通常，数据库具有超出ANSI/ISO SQL标准的扩展和功能，这使得解决问题变得容易 - 但为此，您需要告诉我们您正在使用哪个数据库。 - marc_s

如果他们正在寻找一种与编程语言无关的解决方案，因为他们需要支持多个底层数据库系统，或者数据库实现被抽象化了，那该怎么办？ - dwanderson

嗨@marc_s，我在这种情况下使用PostgreSQL。谢谢提醒。 - johnklee

Answer 1

尝试这个。

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)

- Prince Jea

---

@PrinceJea 实际上这取决于情况。[请点击此处进行澄清] (http://explainextended.com/2009/06/16/in-vs-join-vs-exists/)。 - John Woo

当我有20个数据时，它工作正常，但当我有20000个数据时，它就无法工作了，我现在很困惑。 - Frank

不知道为什么，但它不起作用。我有大约10000行在表中。在我的情况下，@JohnWoo的解决方案完全有效。 - Munam Yousuf

由于"Not In" 方法有数值限制，因此在其中有太多的值将无法起作用。参考链接：http://www.dba-oracle.com/t_maximum_number_of_sql_in_list_values.htm。 - gBusato

我必须这样做：从Table1选择i WHERE i不在（选择i从Table2 where i不为空），且i不为空。 - jaksco

Answer 2

使用LEFT JOIN

SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL

- John Woo

---

我认为这是处理非常大的数据库时更快的方法。 - Ghasem

我的表中有大约50万行数据，这个程序瞬间就运行完了。在发现这个方法之前，我天真地尝试了被认可的解决方案，但是10分钟过去了还没有运行完。 - im-a-teapot

Answer 3

基本上有三种方法： not exists，not in 和 left join / is null。

使用 IS NULL 的 LEFT JOIN

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

不在

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

NOT EXISTS

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

哪个更好？对于这个问题的答案可能最好分解成主要的特定RDBMS供应商。一般来说，当子查询中记录数量的规模未知时，应避免使用select...where...in (select...)。某些供应商可能会限制大小。例如，Oracle有一个1,000的限制。最好的方法是尝试全部三种并显示执行计划。

特别是对于PostgreSQL，NOT EXISTS和LEFT JOIN/IS NULL的执行计划相同。我个人更喜欢NOT EXISTS选项，因为它更好地显示了意图。毕竟，语义是您想找到A中其pk在B中不存在的记录。

老但仍然很有价值，特别针对PostgreSQL：https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/

- L. Holanda

---

https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/ - rc1021

我已将这三个操作整合到了我的存储过程中。第一个建议（使用LEFT JOIN和IS NULL）明显是最快的。 - spankymac

Answer 4

快速替代方案

我运行了一些测试（在 postgres 9.5 上），使用了两个每个约有200万行的表。下面的这个查询性能比其他提出的查询至少快了5倍：

-- Count
SELECT count(*) FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;

-- Get full row
SELECT table1.* FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;

- polvoazul

---

这并没有比@Jhon Woo的解决方案更快。我正在使用Postgres 9.6，使用Jhon的解决方案运行时间约为60毫秒。而我在120秒后放弃了这个解决方案，没有结果。 - froy001

Answer 5

记住@John Woo在上面评论/链接中提到的要点，以下是我通常处理它的方式：

SELECT t1.ID, t1.Name 
FROM   Table1 t1
WHERE  NOT EXISTS (
    SELECT TOP 1 NULL
    FROM Table2 t2
    WHERE t1.ID = t2.ID
)

- CaseyR

Answer 6

SELECT COUNT(ID) FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For count


SELECT ID FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For results

- JoshYates1980