从一张表中选择不在另一张表中 [英] 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 的反连接，您还可以使用易于理解的 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 上有一个索引.

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