Because a SQL query encompasses an arbitrary combination of MANY different sub-programs that are expected to be auto-solved.

Attempt to implement them, manually, and you see how hard is it.

PLUS, not only you need to account for the general solution, but what could be the best considering the current data set.

And, you can't compile statically (dynamically sure).

And, should work interactively, so hopefully be solved faster than run the actual query.

P.D: Joins are normally the focus, but other constructs are also challenging. For example, just solving if and which indexes to pick can be challenging when you have dozens of predicates.

And yes, your optimizer should survive(eventually!) to solve when you get feed hundreds of joins, predicates, aggregates, sorting and arbitrary expressions.

* I worked in the optimizer of a database. EVERYTHING is tricky!