Paul White has fun with a SQL Server optimization:
When the optimizer moves a Group By above a Join it has to preserve the semantics. The new sequence of operations must be guaranteed to return the same results as the original in all possible circumstances.
One cannot just pick up a Group By and arbitrarily move it around the query tree without risking incorrect results.
Read on for the demo, including a couple ingenious tricks the optimizer has up its sleeve.