It's absolutely possible for unity games to have stunning visual.
The problem, like others pointed at, is that it is more used with Indie than AAA.
From my experience 3 main issue come to mind as to why that is. (this maybe a little dated mind you)
1) Unity have frequent update which add/update/remove feature which does not help for stability. So if you develop an AAA stability of the engine is important.
2) The way unity save scenes is not trivial to use with source control and merge tool for code sharing.
3) Unity use C# (which is awesome) but the vast majority of AAA dev still use C++/Unreal. Even tough C# is way more user friendly and readable than C++/unreal dev teams skills and experience would have to completely be renewed which would be both costly and time consumming.
- The updates are not mandatory, so not sure what the issue is.
- This might indeed be somewhat problematic. I don't know how other engines handle it though, so can't say whether other engines have it better, but this certainly seems like it could be better.
- I'm not sure how much of a problem this really is. Lots of devs hate C++ because of how bloated the language has become, although I'm sure sure how widespread this hate exactly is among game devs. That said, picking up a language such as C# shouldn't be much of a hindrance for anyone who knows C++, so it's hard to imagine the cost of picking up a new language being a major factor here. That said, C# might have one issue for performance-critical software such as many games: the garbage collector and the pauses it causes. I would expect the garbage collector to be more of an issue compared to the time it takes to learn a new programming language such as C#.
I'm not sure how much experience you have with these things of course (you were a bit vague), so if I'm wrong, I'd certainly like to hear about it.
the issue with number 1 seems to be resolve now with more stable update cycle. But I remember once that I updated it for much needed improvements only to find out that hitbox collision with concave object was now broke and I used it extensively (it was about 5 year ago). So I guess you could choose not to update but you may be loosing on improvements for stability concern. In the end you'd probably branch the project, update Unity, test extensively, decide if you keep the update or not, and workaround issue if you choose to update. All this process is time consuming and more difficult with larger teams. It certainly had an impact on decision to switch to unity back then.
Funnily enough this company have now got back on it's feet and started using Unity with a specific Dev team. I know that many, mainly older senior employee, are much stubborn and reluctant to the change thought.
But even without the employee/skills factor. this company had to be ready to let go what was probably $millions in investment over many years in tooling/code base shared with many project to start anew.