I hate fan noise too. Sometimes it's necessary, though. But yeah, noise is another trade-off to consider.

Regarding Raspberry Pi specifically, larger, slow-moving fans tend to be quieter than smaller, fast-moving fans. The Pi is compact, so a small fan would be the natural choice.