Subquery not in performance question

Question

I have this slow query

select * from table1 where id NOT IN ( select id from table2 )

Would this be faster by doing something like (not sure if this is possible):

select * from table1 where id not in ( select id from table2 where id = table1.id )

Or:

select * from table1 where table1.id NOT EXIST( select id from table2 where table2.id = table1.id )

Or:

select * from table1
left join table2 on table2.id = table1.id
WHERE table2.id is null

Or do something else? Like break it up into two queries ...

Answer 1

The question is - are the field(s) in the comparison nullable (meaning, can the column value be NULL)?

If they're nullable...

...in MySQL the NOT IN or NOT EXISTS perform better - see this link.

If they are NOT nullable...

... LEFT JOIN / IS NULL performs better - see this link.