3 ms·
You've got this backwards. Assuming the join brings back at most one row (i.e. The join key is a unique key) the left join is better because it can be optimized
by koolba 10y ago
You've got this backwards. Assuming the join brings back at most one row (i.e. The join key is a unique key) the left join is better because it can be optimized differently. The sub select must be done as a nested loop but the join can be done via a hash join. For a full data set (not just one of selected) it'd be more efficient. Simply count the net logical IOs.
- tyfon 10y agoThat is why I think this is baffeling behaviour by sql server since in certain queries you can get an order of magnitude faster execution by dropping left join and using subselect. It's counter intuitive but try it in a real dataset.