You still need fans for water cooling, to cool the radiator. They may be able to run more slowly than on an overworked air cooler, but you also have to consider the noise/vibration from the pump.
It can be quieter or louder, just depends what you choose either way.
Personally, I'm of the opinion that unless you have a specific reason for wanting some sort of water cooling (aesthetics or out of curiosity being perfectly valid reasons
IMO), you should consider saving money and sticking with a half-decent air cooler. Something like a Hyper 212 Evo can be had for ~£25 for example.
For some better recommendations, what sort of CPU are you wanting to cool, and is overclocking something you're considering?