The newest Air uses a different type of fan (as does the retina MBP). It's quieter (perceptibly so) and is nothing like the traditional "whining" fan; it sounds more like white noise. In my rMBP, I'll intentionally max out the fans when playing a game (to reduce heat on my hands) and the sound isn't really noticeable at all. It sounds like the ambient noise from my air conditioner or the highway near my house; in fact it's so close that I literally can't hear the laptop if I'm sitting on my balcony.
That said, I think you're significantly overstating the noise of a modern Mac in the first place. Some of the non-retina MBPs run pretty warn, but my last-gen Air was silent unless I tried to play games on it. Even when writing code. I could build a ~500 file Java project in IntelliJ and it wouldn't even get warm, let alone kick on the fans.