MySQL 从一张表中选择不在另一张表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7596073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select from one table where not in another
提问by Drahcir
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:
所以查询试图做如下：
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
从 R2R.partmaster 数据库中选择不在 wpsapi4.product_details 数据库中的所有 id。我匹配的列是 partmaster.id & product_details.part_num
回答by Johan
Expanding on Sjoerd's anti-join, you can also use the easy to understand SELECT WHERE X NOT IN (SELECT)pattern.
扩展 Sjoerd 的反连接，您还可以使用易于理解的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)
Note that you only need to use `backticks on reserved words, names with spaces and such, not with normal column names.
请注意，您只需要`对保留字、带空格的名称等使用反引号，而不需要对普通列名称使用反引号。
On MySQL 5+ this kind of query runs pretty fast.
On MySQL 3/4 it's slow.
在 MySQL 5+ 上，这种查询运行得非常快。
在 MySQL 3/4 上它很慢。
Make sure you have indexes on the fields in question
You need to have an index on pm.id, pd.part_num.
请确保您有对有问题的领域指标
你需要有一个索引pm.id，pd.part_num。
回答by Sjoerd
You can LEFT JOIN the two tables. If there is no corresponding row in the second table, the values will be NULL.
您可以 LEFT JOIN 两个表。如果第二个表中没有相应的行，则值将为 NULL。
SELECT id FROM partmaster LEFT JOIN product_details ON (...) WHERE product_details.part_num IS NULL
回答by colmaclean
To expand on Johan's answer, if the part_num column in the sub-select can contain null values then the query will break.
为了扩展 Johan 的答案，如果子选择中的 part_num 列可以包含空值，则查询将中断。
To correct this, add a null check...
要更正此问题，请添加空检查...
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)
- Sorry but I couldn't add a comment as I don't have the rep!
- 抱歉，我无法添加评论，因为我没有代表！
回答by Drahcir
So there's loads of posts on the web that show how to do this, I've found 3 ways, same as pointed out by Johan & Sjoerd. I couldn't get any of these queries to work, well obviously they work fine it's my database that's not working correctly and those queries all ran slow.
所以网上有很多帖子展示了如何做到这一点，我找到了 3 种方法，与 Johan & Sjoerd 指出的相同。我无法让这些查询中的任何一个工作，很明显它们工作正常，这是我的数据库工作不正常，并且这些查询都运行缓慢。
So I worked out another way that someone else may find useful:
所以我想出了其他人可能会觉得有用的另一种方法：
The basic jist of it is to create a temporary table and fill it with all the information, then remove all the rows that ARE in the other table.
它的基本原理是创建一个临时表并用所有信息填充它，然后删除另一个表中的所有行。
So I did these 3 queries, and it ran quickly (in a couple moments).
所以我做了这 3 个查询，它运行得很快（在几分钟内）。
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`
)
