CPU Fan

As a small, high-speed fan, the CPU fan is often the loudest component in a PC. There are several approaches to reducing fan noise. As with any other component, lower power CPUs require less cooling than higher-power ones. Less cooling means less (or no) noise.

Via C3

VIA's Cyrix division shipped the C3, a low-end CPU explicitly designed to be run fanless (so as to save the system manufacturer the cost of a fan). The performance is poor compared to most any other chip on the market, but acceptable for most uses. See my review for details.

Underclocking

If you don't want to go with a C3, you can try underclocking. As such, one can underclock a CPU so it uses less power and use a large heatsink without a fan. For a long time, my personal machine was a K6/2-400 underclocked to 300, so it burned 25% less power. When underclocking, I was also be able to reduce core voltage, so it burned even less power (mobile CPUs explicitly designed for this can have nearly quadratic reductions in power usage with speed instead of linear with speed). If you reduce voltage too much, the computer will sporadically crash or just won't work, but you can't permanently damage most CPUs this way, so this may be something worthwhile to experiment with. Most BIOSes include a utility to monitor CPU temperature, and CPU makers have specs for maximum temperatures on their web sites.

Other chips

CPU makers usually place specs for power usage on their web sites, and a global listing of specs can be found here. Often times, the same CPU will have several variants (called steppings), each with different power usage (a K6/3-400AHX uses 16.1W, while the K6/3-400AFR uses only 10.8A). Many resellers will let you order a specific stepping. Extremists can also try to order mobile versions of processors, which often use considerably less power, and can often tolerate higher temperatures, but cost considerably more, and sometimes require non-standard motherboards.

Around the corner, Transmeta's chips look like they could be pretty exciting, but we are a ways from seeing them in reasonable desktop machines.

Testing

Once you've killed the fan, do some intensive tasks (such as video games) for a couple of hours, while monitoring CPU temperature. There is also a variety of programs that do CPU burn-in tests that try to tax the CPU to burn as much power as possible.

If, after doing the burn-in test, it is within a safe margin of the CPU's max temperature (again, either from the manufacturer's web site or here), keep the changes. If not, underclock a bit and try again.

Use PSU fan instead

In some machines made by Dell (and by recommendation that came with some copies of Intel's ATX spec), the CPU is placed directly below the power supply fan's intake or outtake. The fins on its heat sink are aligned such that the power supply fan blows directly through them. With a larger heat sink, this can provide sufficient cooling. If you're not eliminating your PSU fan as well, this approach works well.

If you are not ready to kill the fan, you can try to at least reduce its noise in a variety of ways:

Several heat sink/fan makers make quiet models. Tom's Hardware Guide and Heat Sink Guide review models
Again, underclocking your CPU and reducing its core voltage will make it burn less power. You can then use a fan on a lower setting.
Putting some spacing between the fan and heatsink will significantly reduce the cooling power, but may also significantly reduce noise, as the fan is less loaded.