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中只有10万行，而B中则少了3-10k，大约2秒)

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屋！