The basic answer is ionization potential: Gases with a high ionization potential (helium, Oxygen, CO2) tend to be great at lowering the surface tension and increasing the arc temperature. They tend to give you much a narrower deeper weld bead with the same exact parameters as argon.
However reactive gases like oxygen and CO2 tend to bind to alloying elements or oxidize the base material. For carbon steel CO2 is generally find because it's not terribly oxidizing and carbon pickup in carbon steels is generally not a problem. For this reason it should never be used for stainless steels as it leads to carbide precipitation.
Now as far as more exotic metals especially corrosion resistant ones like inconels, titanium and zirconium. They are extremely reactive, which means they will gladly suck up any last bit of oxygen and carbon and then become extremely brittle or crack. At the least they will strip out alloying elements and ruin mechanical properties.
All that being said, Helium or a mixture of helium and argon can be used just fine on carbon steels, but most don't do it for the cost.
flux core wire is a different beast altogether and tends to "require" CO2