brymen (11/12/2013)
...If the article ended with deroby's post, It would be a 5 star article. It has been my experience that WHERE NOT EXISTS always meets or beats a LEFT OUTER JOIN ... WHERE <somefield> IS NULL query...
I agree. It took me a while to come around on this one, because it seems like you might be creating a hidden loop with WHERE NOT EXISTS, but often the query plan is identical to LEFT OUTER JOIN. In cases where there could be many matches (eg. employees could take a course many times), WHERE NOT EXISTS is faster because the server can stop "looking" when it finds the first match, but my understanding is that LEFT OUTER JOIN retrieves all matches first and then applies the "WHERE <somefield> IS NULL" to filter them out.
Does anyone know if this has always been true, or was there a time in the dark ages of MS SQL server when WHERE NOT EXISTS was slower than a LEFT OUTER JOIN?