I have a query that runs quite slow (see below). While searching for a way to improve it we found that the query runs about ten times faster if we replaced the LEFT OUTER JOIN with LEFT HASH JOIN
The result seems to be the same. Is it? In what cases wont it return the same result? What difference are there? Is there something that i should look out for when running a LEFT HASH JOIN instead of a LEFT OUTER JOIN?
SELECT t.foo, t.bar, t.data FROM (dbo).(Table) as t LEFT OUTER JOIN (ABC).(ExternalTable) as s ON s.foo = t.foo and s.bar = t.bar and s.data = t.data WHERE s.foo is null and s.bar IS NULL and s.data IS NULL