Depends upon the situation.

For example, having a cartoon style in a game like Modern Warfare would completely kill the setting. Cartoon style does allow a lot of freedom, yes, but it can restrict you to certain settings. The realistic style allows you to have other settings, like war-beaten battlefields or horror-themed dungeons. However it too can restrict you to certain settings that the cartoon style could handle easily.

In any case, no one style is better than another. If you're creating a game with a specific theme, it will only work with a few select styles.