Idealistic designers care about whether the end-users (meaning the people who will, say, visit the website) like the design.
Pragmatic designers care about whether the client likes the design.
It's quite a bit harder to make money as an idealistic designer, unless you can wield conversion rate data at the client. If you're just one designer on a team working for an agency, who's been given a "brand policy" document by the client? No chance.
[Top tip - never ask the client "What do you think of X?" instead ask "What would your customers think of X?". You'll be amazed at the different kind of responses you get.]
Should a site look good? Absolutely. It never hurts anything to look better. Can a site look ugly? Absolutely. If your business is strong enough, there's no amount of bad design that can hurt it.
The main thing though, is that more attractive designs don't necessarily convert better. People think they do, because you're turned off by ugly designs, and for sure, if you've got two sites up that share the same interface, and one is prettier than the other, the prettier one will likely win your business. But if you take those same sites, and have better copy on the ugly site, it may well convert better. If your pretty design is hard to use, it may well lose.
So yeah, if your web designer cares more about the end user or themselves than whether the design is effective, then you're paying for the wrong thing.
But I'm nitpicking. From what I've seen, in 99% of cases looking more pro helps.
You have there, in a nutshell, the difference between the outlook of good and bad designers.
Bad designers care about whether they like the design.
Good designers care about whether the end-user likes the design.