Mysql select where not in table

BCS_1343 0
Viewed 70253

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?

not-exists join mysql
5 Answers

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

SELECT A.*    
from A left join B on 
    A.x = B.y
    where B.y is null
user_170

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

Bill_Karwin_208600

I also use left joins with a "where table2.id is null" type criteria.

Certainly seems to be more efficient than the nested query option.

user_1070670
Related