从一个表中选择不在另一个表中的数据

Question

我试图找到一个表中存在但另一个表中不存在的行，这两个表在不同的数据库中，并且对于我用来匹配的列，它们也具有不同的列名。

我有一个查询语句，如下所示的代码，我认为它可能有效，但速度太慢：

SELECT `pm`.`id`
FROM `R2R`.`partmaster` `pm`
WHERE NOT EXISTS (
    SELECT * 
    FROM `wpsapi4`.`product_details` `pd`
    WHERE `pm`.`id` = `pd`.`part_num`
)

因此，该查询尝试执行以下操作：

从R2R.partmaster数据库中选择所有不在wpsapi4.product_details数据库中的id。我要匹配的列是partmaster.id和product_details.part_num。

- Drahcir

---

对我来说，存在/不存在是最好的方式，因为它清楚地表达了你想要获取什么。但似乎这是最慢的方式（在MySQL上）。请参考：http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ - DavidEG

Answer 1

在Sjoerd的反连接（anti-join）基础上，你也可以使用易于理解的SELECT WHERE X NOT IN (SELECT)模式。

SELECT pm.id FROM r2r.partmaster pm
WHERE pm.id NOT IN (SELECT pd.part_num FROM wpsapi4.product_details pd)

请注意，只有在保留字、含有空格等情况下才需要使用反引号`，而普通列名则不需要。

在MySQL 5及以上版本中，这种查询速度很快。
在MySQL 3/4中，它的速度较慢。

确保你的字段上有索引
你需要在pm.id和pd.part_num上创建索引。

- Johan

---

谢谢你的回答，它没有快速地工作，但查询很好，只是不适用于我的数据库。我已经想出了另一个解决方案，但是StackOverflow要等3个小时才能让我发布。 - Drahcir

提醒一下，类似上述查询在MySQL 5.1.73a上大约需要10分钟的时间。当我升级到MySQL 5.6.22后，只需要大约456毫秒。 - Mauvis Ledford

请参考@colmaclean的答案以处理空值。 - parameciostudio

我唯一要添加的是在内部选择查询中加上“WHERE pd.part_num IS NOT NULL”。 - David Moritz

Answer 2

你可以使用LEFT JOIN连接这两个表。如果第二个表中没有相应的行，则值将为NULL。

SELECT id FROM partmaster LEFT JOIN product_details ON (...) WHERE product_details.part_num IS NULL

- Sjoerd

Answer 3

进一步解释Johan的回答，如果子查询中的part_num列可以包含空值，则查询将会出现问题。

要纠正这个问题，请添加一个空值检查...

SELECT pm.id FROM r2r.partmaster pm
WHERE pm.id NOT IN 
      (SELECT pd.part_num FROM wpsapi4.product_details pd 
                  where pd.part_num is not null)

抱歉，由于我的声望不够，无法添加评论！

- colmaclean

---

不在确定中可能会很糟糕。 - Drew

Answer 4

因为网络上有很多关于如何做这件事的帖子，我找到了三种方法，就像Johan和Sjoerd指出的那样。我无法使这些查询工作，显然它们可以正常工作，但是我的数据库没有正确地工作，并且这些查询都运行缓慢。

所以我想出了另一种方法，其他人可能会发现有用：

基本思路是创建一个临时表并填充所有信息，然后删除在另一个表中已存在的所有行。

所以我执行了这三个查询，它运行得非常快（几秒钟内）。

CREATE TEMPORARY TABLE

`database1`.`newRows`

SELECT

`t1`.`id` AS `columnID`

FROM

`database2`.`table` AS `t1`

.

CREATE INDEX `columnID` ON `database1`.`newRows`(`columnID`)

.

DELETE FROM `database1`.`newRows`

WHERE

EXISTS(
    SELECT `columnID` FROM `database1`.`product_details` WHERE `columnID`=`database1`.`newRows`.`columnID`
)

- Drahcir

---

它运行得很快，因为它只比较了第一个产品，或者我看错了吗？！ - MPaulo

Answer 5

我找到的简单解决方法如下：

SELECT
    first_table.*
FROM
    first_table
    LEFT JOIN second_table ON second_table.common_column = first_table.common_column 
WHERE
    second_table.common_column IS NULL;

- Albert Alberto