One more point in the "Anti Join". Use EXISTS instead of IN and LEFT JOIN if you only want to check existence of a row in another large table / subquery based on the conditions. EXISTS returns true as soon as it has found a hit. In case of LEFT JOIN and IN engine collects all results before evaluating.
Yeah, I was a bit confused there. In all my testing, (NOT) EXISTS was generating either a better plan or the same one as (LEFT) JOIN/(NOT) IN. In addition, it’s also clearer what the intent is.
reply