CODEWITHSUNDEEP
sqlmysqlperformancesubquery
Mark Steudel
Mark Steudel

Reputation: 1682

Subquery not in performance 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 ...

Upvotes: 5

Views: 1705

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 8

Kevin Stricker
Kevin Stricker

Reputation: 17388

select table1.* from table1 
LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.id IS NULL

The object being to get rid of NOT IN

Upvotes: 1

Related Questions