The difference is that with SQL, while you almost never have to worry about forcing the optimizer to work a specific way, you have the option of doing so in those very rare cases where you should.

For example, in a database I used to work with, there was a query that, depending on the account number it read, would either have to deal with a very small number of rows (usually 5-10), or possibly with a much larger number of rows (up to 60,000 in some cases). Since SQL stores execution plans and re-uses them, it ran into problems if it tried to use the optimum plan for the small number of rows when it was dealing with a large number, or vice versa.

If it was using the small plan on the large number of rows, it could take up to 30-40 seconds to complete. If it used the big plan on the small number of rows, it would only take a few seconds. If it used the right plan on the right number, either one, it would take milliseconds.

By adding a couple of simple hints, I got a compromise solution that was slightly slower on each than the optimum, but much faster than the opposite plan. So I ended up with either one taking just under half a second, which was a major improvement over the Russian Roulette of the just leaving it to pick it on its own.

In nine years of being a DBA, that's the only time I've ever had to bypass the engine on picking an execution plan.

So, it's very rarely useful, but when it is, it can be quite an improvement.