如何从一个表中选择所有在另一个表中不存在的记录？

Question

表1（id，name）
表2（id，name）

查询语句：

SELECT name   
FROM table2  
-- that are not in table1 already

- z-boss

---

看看底部使用 UNION 的解决方案，比这里列出的任何其他解决方案都快上几个数量级。 - fede72bari

Answer 1

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: 这里发生了什么？

A: 在概念上，我们选择table1中的所有行，并尝试查找与name列具有相同值的行。如果没有这样的行，则对于该行，我们将只在结果中留下table2部分为空。然后，我们通过选择只有匹配行不存在的结果中的那些行来限制我们的选择。最后，我们忽略结果中除name列（从table1确定存在）之外的所有字段。

虽然这可能不是所有情况下最有效的方法，但它应该在几乎每个试图实现ANSI 92 SQL的数据库引擎中都可以运行。

- Kris

---

@z-boss：它在SQL Server上的性能也是最差的：http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ - OMG Ponies

左连接允许右边的行不存在，而不影响左边行的包含。内连接要求左边和右边都存在行。我在这里做的是应用一些逻辑来基本上获得内连接的反向选择。 - Kris

哦，这有助于非常容易地进行可视化，其他人可能用了五种不同的方式，但这个方法很有帮助。简单来说：首先进行左连接，将A中的所有内容和与A匹配的B中的所有内容都获取出来。但是在左连接中，不能够匹配到的字段就为空值。然后你说，好的，我只想要那些为空值的数据行。这样，你就可以得到A中所有没有在B中匹配到的数据行了。 - Muhammad Umer

需要注意的是，我认为这个解决方案（被接受并投票）是唯一一个可以针对多个字段同时发挥作用的情况进行编辑的解决方案。具体来说，我正在从表1返回字段、字段2、字段3，其中字段和字段2的组合不在第二个表中。除了修改此答案中的连接之外，我没有看到其他“更有效率的答案”可以实现它。 - TMWP

请确保使用"WHERE t2.name IS NULL"而不是"AND t2.name IS NULL"，因为"and"将无法给出正确的结果。我不太明白为什么，但这是事实，我已经测试过了。 - user890332

Answer 2

你可以选择

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

或者

SELECT name 
FROM table2 
WHERE NOT EXISTS 
    (SELECT * 
     FROM table1 
     WHERE table1.name = table2.name)

参见此问题，了解三种实现此操作的技巧。

- froadie

---

处理大量数据时速度非常慢。 - Lightbulb1

是的，确实非常慢。 - sirus

在not exists查询的子查询中，应该是“from table1”吧。 - Hound

非常困惑这个帖子怎么会得到那么多赞。我很难想出一个使用它的理由，因为有一种解决这个问题的方法是非常快速的，而且键入的数量大致相同。 - searchengine27

@searchengine27 当我们有查询优化器时，它真的会那么慢吗？ - user749127

Answer 3

我没有足够的声望点数来投票赞同 froadie的答案。但是我不同意对 Kris的答案 的评论。以下是答案：

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

实际上要高效得多。我不知道为什么，但是我正在处理80万条记录，并且与上面发布的第二个答案相比，差异非常大。只是我的两分钱。

- Tan Rezaei

---

NOT IN 查询中，子查询只执行一次；而在 EXISTS 查询中，子查询会为每一行数据分别执行。 - Carrick

谢谢夸奖 :) 这样我用左连接将我的 25 秒查询转换为只需 0.1 秒。 - Bassem Shahin

答案并没有特定的顺序，因此第二个答案并不意味着它是你所想象的那个答案。 - user177800

我认为这也可能是唯一的解决方案，如果您想向子查询添加一些额外的过滤器/条件。 - Dre

2023年，这仍然是我最满意的答案，因为如果使用这个查询，它只需要0.1秒钟。 - BlackSD

这确实是一个令人惊叹的选择。对我来说，它就像魔法一样起作用，在SAP HANA数据库中，表1有570万个匹配条目，表2大约有300万个条目。 - kaushal

Answer 4

SELECT <column_list>
FROM TABLEA a
LEFTJOIN TABLEB b 
ON a.Key = b.Key 
WHERE b.Key IS NULL;

如何在MySQL中连接两个表格

- Anuraj

---

解释连接（JOIN）时，不要使用维恩图，应该用更易懂的方式表达。请看这篇博客：Say NO to Venn Diagrams When Explaining JOINs。 - Tony

很遗憾，交错图比维恩图更不清晰且更难以直观理解。 - GreySage

谢谢您提供的图表。 - Neoheurist

Answer 5

这是纯集合论，你可以使用 minus 操作来实现。

select id, name from table1
minus
select id, name from table2

- Winter

---

你认为这比左连接更有效率吗？ - uhs

应该是这样的。减去命令是为了处理这种情况而设计的。当然，判断特定数据集的最佳方法是尝试两种方式并查看哪个运行更快。 - Winter

在T-SQL中，集合运算符是“except”。这对我来说非常方便，并没有导致任何减速。 - user4864716

在SQLite中，“minus”运算符也可以表示“except”。 - lifjoy

MySQL不支持MINUS运算符。 - Muhammad Azeem

Answer 6

这是最适合我的方法。

SELECT *
FROM @T1
EXCEPT
SELECT a.*
FROM @T1 a
JOIN @T2 b ON a.ID = b.ID

这比我尝试过的任何其他方法都快了两倍以上。

- Bob

---

谢谢，这对大量数据也很有效！但我只是想知道“Except”这个术语。 - PatsonLeaner

在200k条记录中，我对5k条记录的处理只用了767毫秒。其他所有操作都需要几分钟的时间。 - sarlacii

关于 T-SQL 中的 EXCEPT https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver16#syntax - Gergo

Answer 7

注意潜在陷阱。如果 Table1 中的 Name 字段包含空值，那么你可能会遇到一些惊喜。 更好的做法是：

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT ISNULL(name ,'')
     FROM table1)

- user4872693

---

COALESCE > ISNULL（ISNULL是T-SQL语言中无用的附加项，与COALESCE相比没有任何新的或更好的功能） - Kris

Answer 8

您可以在mssql中使用EXCEPT或在oracle中使用MINUS，根据http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/的说法，它们是相同的。

- Izzy

Answer 9

那对我来说很有效。

SELECT * 
FROM [dbo].[table1] t1
LEFT JOIN [dbo].[table2] t2 ON t1.[t1_ID] = t2.[t2_ID]
WHERE t2.[t2_ID] IS NULL

- David Fawzy

Answer 10

所有上述查询在大型表上的速度非常缓慢。需要改变策略，这里有我用于我的一个数据库的代码，你可以转换更改字段和表名。

以下是策略：

1. 创建两个隐式临时表并将它们合并。 2. 第一个临时表来自选择第一个原始表的所有行，其字段您想要控制，并且未出现在第二个原始表中。 3. 第二个隐式临时表包含所有两个原始表的行，这些行具有您想要控制的相同值/列。 4. 联合结果是一个表，如果在两个原始表中都有该值的匹配项（一个来自第一个选择，另一个来自第二个选择），则具有相同控制字段值的多个行，并且如果第一个原始表的值不与第二个原始表的任何值匹配，则具有一个控制列值的行。 5. 进行分组和计数。当计数为1时，表示没有匹配项，最后，仅选择计数等于1的行。

看起来不太优雅，但比以上所有解决方案快得多。

重要提示：启用要检查的列上的索引。

SELECT name, source, id
FROM 
(
    SELECT name, "active_ingredients" as source, active_ingredients.id as id 
        FROM active_ingredients

    UNION ALL
        
    SELECT active_ingredients.name as name, "UNII_database" as source, temp_active_ingredients_aliases.id as id 
    FROM active_ingredients
    INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

) tbl
GROUP BY name
HAVING count(*) = 1
ORDER BY name

- fede72bari