coderamblings.com

To decide whether to use 'NOT IN' or a left join, to find out all the rows in a table that did not have a relationship to another table, e.g. find all houses with no kittens.

I used the benchmark() mysql command to compare, in summary there was not much difference at all, using the mysql command prompt from localhost.

Statement 1
select benchmark(10000000000,'select * from houses where id not in (select house_id from kittens) ORDER BY id');
18.47s
18.51s
18.49s
18.52s
18.52s

Statement 2
select benchmark(10000000000,'select houses.* from houses left join kittens on houses.id=kittens.house_id where kittens.house_id is NULL ORDER BY houses.id');
18.51s
18.49s
18.50s
18.50s
18.51s