Greetings!
I will do everything possible to avoid the whole air vs. water cooling debate, for it really comes down to personal taste. For many it is simply an aesthetic choice...some prefer the look of an air cooler, and some prefer the look of water cooling.
A very decent AIO is the Deepcool Captain 240, which can usually be found for less than $100. I have two in my system (dual xeon...totally unnecessary, but I have a glass case and went with a white LED motif), and they are very quiet and keep my xeons super cool under full 3D rendering situations. I picked them up for $89 from Newegg on sale...they are pricey there now, but with a quick google search I found many retailers with offerings sub $100.
It really comes down to what you want to spend and if looking at it in your case has any visual requirements. There are really good air coolers out there, and really good AIOs, and it really is a personal choice. Overclocking will create more heat, but I have not heard of a decent reputable modern cooler ever having troubles keeping temps in line, and yes cpus get warm, but not as much as they used to.
The chart and advice provided by Isokolon is great and gets no argument from me, but I figured I would just toss in my personal experience with the AIOs I selected.
I say, your personal taste is the most important factor in this decision, especially if you have a windowed case. The only case I can make for AIO cooling is that if you have a warm running system you can isolate and quarantine the CPU cooling from the rest of the environment due to the fact that you can set the fan(s) to intake thus using only cool air from the outside to cool the CPU. Air coolers utilize the air inside your case which is probably already warm...Not an issue if there is good airflow, but in some small form factor situations with a hot running GPU, this may be a real consideration.
Hope this helps!