Professors Stephen Reucroft and John Swain from the Department of Physics at Northeastern University offer the following explanation:

Low-pressure sodium vapor lamps cast a soft yellow light on certain San Diego streets
Image: CITY OF SAN DIEGO, STREET DIVISION
YELLOW GLOW. Low-pressure sodium vapor lamps cast a soft yellow light on certain San Diego streets.

Any element placed in a flame will change its color. Atoms are made of positively charged nuclei, about which negatively charged electrons move according to the laws of quantum mechanics. Quantum mechanics constrains them to appear in various distinct patterns, called orbitals. (Orbitals are a lot like planetary orbits, but blurrier, so that you're never quite sure just where the electrons are.)

Left on their own, the electrons of an atom tend to relax into orbitals that leave the atom with the lowest possible energy--its ground state. Putting atoms into a flame, though, adds energy to the looser electrons farthest from the nucleus and pushes them into other orbitals. Eventually, these excited electrons drop back to where they ought to be, and in so doing, they release the energy they stored up as particles of light, called photons.

The color of the light emitted depends on the energies of the photons emitted, which are in turn are determined by the energies required to move electrons from one orbital to another. A flame has lots of different energies existing within it all the time, and every so often, it gets lucky and has the right quantity present to push an electron from one orbital to another. When the electron drops back, it must release the same exact amount energy that it absorbed. Depending on the element you put in the flame, various different energies of photons (colors) will appear. Those colors are as distinctive to each element as fingerprints are to people.

As an easy experiment to try at home, put a little table salt in a flame. You will see the same yellow glow as that of a sodium street light, in which the sodium electrons are kicked about by an electric current instead of a flame. Also, a bit of boric acid, which you can get at a pharmacy, will produce a lovely green flame due to the element boron.