I'll start with the short...
Adding air to a pond has a huge impact on the health of the ecosystem: It circulates the water, disrupts mosquito breeding, adds oxygen for fish and other critters, and it helps prevent algae and other weeds. Aeration is by far the best bang for your buck in terms of pond health. And did I mention less mosquitoes?
Now that I have your attention, here's the long of it..
Aeration has many forms - fountains, windmills, aerators, circulators and electric compressors, and more. All these options have their pros and cons.
Windmills, Electric Compressors
Compressors are visually minimal (with the exception of a windmill itself). They provide compressed air flow down an airline into the bottom of the pond, where the air is forced through a diffuser stone that breaks it up into tiny bubbles.
These tiny bubbles provide bottom-up aeration which is the most efficient; the bubbles have a long way to travel through the water, maximizing oxygen transfer into the water. An addition, as they rise they spread out into a cone, oxygenating the largest surface area of pond per unit of electricity.
As bubbles rise from the bottom of the pond, they drag water up with them. Water from the surface then moves to the bottom to replace it, and as such creates a circular motion, stirring the whole pond. Circulation of water from the bottom up prevents stratification - oxygen actually makes it down to the bottom of the pond. Stirring and oxygenating the deep water allows a faster and more complete break down of wastes like decaying plant matter, fish manure, leaves, twigs, etc. This means you'll have less smelly gasses and water, and best of all: less algae.
Electric compressors need to be well ventilated and most need to be housed in a clean and dry compartment - like a doghouse, barn, or shed. Compressors can be ran year-round or seasonally, depending on your goals (algae control, winter kill, deicing, etc.)
Aerators and Fountains
This type of aeration provides visuals - fountains especially, aerators to a far lesser degree. Both units hang under a donut-shaped float and use a motor-driven propeller to throw water up into the air. The difference between a fountain and an aerator is that a fountain forces the water at high pressure through a nozzle that creates a specific display, whereas the aerator has no nozzle.
This style of aeration does not provide the same oxygenation at the bottom of the pond as an air compressor, and as such, isn't as successful at fighting algae and weed growth. These will circulate the top layers of water, which helps keep the pond surface free of scum and tree pollen. The benefit of this is that the do not disturb the lower levels of water - this is good for ponds with trout and other cold-loving fish. Another advantage with fountains and aerators is that they do a superior job of aerating shallow water bodies where you would otherwise need a large number of diffusers for an air compressor.
Water is much heavier and more dense than air, so understandably it would take more energy to move it when compared to air. This is why fountains, aerators, and circulators all require more electricity than an air compressor. This should be taken into consideration when considering which to purchase.
Lastly, fountains can be seriously damaged in freezing conditions, and as such are only seasonal water features.
Similarly to fountains and aerators, circulators turn over the top layers of water without disturbing the bottom. Circulators are motor-driven propellers that hang horizontally under a black plastic float, providing thrust for a stream-like environment. These are popular for moving debris, or keeping debris from floating into a bay. As mentioned above, circulators require more electricity to run than a compressor would. Because of the float at the surface of the water, circulators can be damaged in freezing conditions and should only be used when temperatures allow.