SQL Performance Tuning

When you use subqueries, you are giving the DBMS a weak "hint" about processing plans, but the DBMS still has plenty of scope for decision making. Each of the possible plans may be best for certain situations so a blanket statement like "always flatten" would be facile. But when you know the situation, and you know how to influence the plan, you can do some of the decision making yourself.