That 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

That 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.