Watercooling started out as an 'extreme' mod to pc's for cooling overclocked cpu's. You have to remember that back then, there were no all in one coolers like the h100i or h55. If you had watercooling, you built your own from the ground up. Some were more polished than others, some were sort of homebrew watercooling. The alternative were fairly crappy stock coolers or slightly better air coolers that still relied on aluminum heatsinks and wouldn't cope with the overclocking temps.
Move forward some and two things have happened. Water cooling has become mainstream and commercial, anyone can have a watercooled rig because they can buy an all in one unit that goes together just like an air cooler. Attach the rad/fan and lock down the cooling block and ta-da, 'custom' water cooled. Aio units don't cool as well as custom loops using individual parts.
The other advancement has been on the front of air cooling. Now air coolers feature copper direct touch heat pipes containing chemicals that work through convection inside those tubes. It boils and carries heat to the outermost parts away from the heat source and as it cools, flows back down to the base plate in a continuous cycle. They're larger than they used to be with 2 and 3 fans on them now. What this means is that unless you spend several hundred on a real watercooling setup, the ~$100-150 range of air coolers and aio watercooling don't perform much differently. An aio with a 240mm or larger radiator will typically cool better under stress (for extended periods of gaming while overclocked) than an air cooler but for the most part their performance is equal.
Cpu's today don't typically produce as much heat as they used to, they're becoming more energy efficient. At the same time, the lithography is changing and the die's are getting smaller. The smaller the die, the more rapidly a fairly cool chip will heat up under oc conditions and the headroom for overclocking isn't what it once was. Water cooling isn't really necessary like it used to be and if the overclock is pushing a new cpu that hard likely it's for benchmarks and using ln2 since water can't keep up in those extremes. Some prefer to incorporate peltiers for cooling (think refrigeration) since water cooling can only cool at most to ambient. It's not energy efficient though, purely for performance.
Two most common looks at it, since aio coolers and big air coolers have similar performance - if you're going to lan parties and carrying your rig around, water cooling might be a better option. Less stress on the motherboard pcb around the cpu socket. But there's the chance of a leak and severe sucky things happen when they leak.
Big air coolers don't run the risk of leaking, but they're heavy and better for systems that stay in place. One isn't quieter than the other since water cooling requires radiator fans (especially if you get into 240/360/480 rads with push/pull configuration). You're talking 4-8 additional fans. It depends on what someone prefers for looks.
Air cooling is less complicated, the only moving part is the fan and at most you might have to blow the dust out of it once in awhile. Aio coolers, there's no way to really monitor their performance. With a real water cooling loop, usually it involves clear tubing, people incorporate flow meters to make sure the coolant is moving at the appropriate rate (they can see if it slows down there may be a clog in the system), they can view the coolant level in the reservoir and there's maintenance required. Roughly every year or two the entire system needs drained and flushed, cleaned out and put back together. Also if you're running a custom loop chances are it has it's own power source and is powered on independantly from the pc. There's the potential of accidentally forgetting if you're not used to it and turning on your pc with the cooling system off.
Those are about all the pros/cons I can think of either way. It's always a personal choice. For me I prefer air. With the limitations of modern overclocking, the gains don't justify the added cost and I think aio's are a bit pricey for the performance they offer. If I was going to go with water cooling, I'd go with a real cooling loop (separate pump, res, rads etc). It's costly. I'm ok with a few less mhz and not chancing hardware I can't afford to replace to a water leak. I also don't move my case around and the looks of big air coolers don't bother me. If I attended lan parties, it might be a different story.