Water cooling will for sure give you better temps (With some very few exceptions) But a good Air Cooler Heat sink will give you very good results too without being so expensive.
To give you an example when I was running a i7 860 @ 4.2 Ghz and two HD 6950's On air the temps were
i7 @4.2Ghz = 79c With LinX stress test
6950's Stock =85c under stress test Kombustor.
Under Water same systems same tests....
i7@ 4.2Ghz = 65c With LinX Stress Test
6950's Stock = 41c Under Stress Test Kombustor.
As you can see a huge difference.... But also the Heat Sink was $70 bucks and the Water Cooling setup was about $300 or more.
So in the end it depends on what you are going for and what you want to accomplish.