• /
• ~
• ls
• whoami
• gpg
• ?
• blog
• rss

Mysql select where not in table

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);

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;

On my data this seems to run slightly faster (~10%) but what about in general? answered Dec 9 '08 at 20:47 Bill Karwin

I think your last statement is the best way. You can also try

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

# tags