Mysql选择不在表中的位置 [英] Mysql select where not in table

问题描述

我有 2 个表(A 和 B)具有相同的主键.我想选择 A 中而不是 B 中的所有行.以下工作:

I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works:

select * from A where not exists (select * from B where A.pk=B.pk);

但是它看起来很糟糕(A 中只有 100k 行约 2 秒，B 中少 3-10k)

however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B)

有没有更好的方法来运行它?也许作为左连接?

Is there a better way to run this? Perhaps as a left join?

select * from A left join B on A.x=B.y where B.y is null;

在我的数据上，这似乎运行得稍微快一些(~10%)，但一般情况下呢?

On my data this seems to run slightly faster (~10%) but what about in general?

推荐答案

我以您第二个示例的格式使用查询.联接通常比相关子查询更具可扩展性.

I use queries in the format of your second example. A join is usually more scalable than a correlated subquery.

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