Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:

SELECT table1.id FROM table1 WHERE table1.a='FOO' AND 
NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

or

SELECT table1.id FROM table1 LEFT OUTER JOIN table2 
ON table1.id = table2.id WHERE table1.a='FOO' AND table2.id IS NULL;
share|improve this question
1  
Efficiency is similar. Depends on the version of mysql and your specific situation (table sizes, distribution.) I suggest you test both in your application but don't expect to see major differences. More like a 20-30% diff at maximum. –  ypercube Mar 22 at 13:05

1 Answer 1

NOT EXISTS performs in most straightforward way: just checks equality and returns TRUE or FALSE on the first hit / miss.
LEFT JOIN / IS NULL either makes an additional table look-up or does not return on the first match and performs more poorly in both cases.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.