"Not exists" is faster than "left outer join where is null"

In case you didn't know, if you want to retrieve rows from a table that doesn't have a connection to another table, you can write a left outer join, and then in the where clause, put "where table2.column is null"?

Like this!

This will get all posts within a few days from users that has never left any comment.

Neat, right? A lot of people seem to be doing this. I don't know why. Doing this will make SQL Server read every single row from the left outer join table and then proceed to filter out the null values. That's bad.

Is that 43 million rows read?

You know what's not bad? Not exists! ...and I guess "Not in" works fine too...

Not exists will process the rows in a different way and end up reading a whooooole lot less data.

Phew... Much better...

Unless you're planning on using the data from that left outer join, stick with not exists!