从一个表中选择而不在另一个表中 [英] Select from one table where not in another

问题描述

我正在尝试查找一个表中的行，而不是另一表中的行，这两个表都在不同的数据库中，并且在我要用来匹配的列上也有不同的列名.

I'm trying to find the rows that are in one table but not another, both tables are in different databases and also have different column names on the column that I'm using to match.

我有一个查询，下面的代码，我认为它可能有效，但是速度太慢:

I've got a query, code below, and I think it probably works but it's way too slow:

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

因此查询尝试执行以下操作:

So the query is trying to do as follows:

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

Select all the ids from the R2R.partmaster database that are not in the wpsapi4.product_details database. The columns I'm matching are partmaster.id & product_details.part_num

推荐答案

在Sjoerd的anti-join上进行扩展，您还可以使用易于理解的SELECT WHERE X NOT IN (SELECT)模式.

Expanding on Sjoerd's anti-join, you can also use the easy to understand SELECT WHERE X NOT IN (SELECT) pattern.

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

请注意，您只需要对保留字，带空格的名称(而不是普通的列名)使用`反引号.

Note that you only need to use ` backticks on reserved words, names with spaces and such, not with normal column names.

在MySQL 5+上，这种查询运行得非常快.
在MySQL 3/4上，它很慢.

On MySQL 5+ this kind of query runs pretty fast.
On MySQL 3/4 it's slow.

确保您在相关字段上有索引
您需要在pm.id，pd.part_num上有一个索引.

Make sure you have indexes on the fields in question
You need to have an index on pm.id, pd.part_num.

这篇关于从一个表中选择而不在另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！