Curcuma longa is technically yellow turmeric, while C. aromatica is wild (white) turmeric. C. aromatica has an upright purple/mauve flower that flares out at the top and appears in spring, C. longa has a large, upright, more or less cylindrical white flower with yellow interior that appears in autumn. Yellow turmeric and red turmeric are both Curcuma longa, the 'red' being a variant from Papua, but it is generally more deep orange than red. The second year turmeric root (technically ‘rhizome’) of yellow turmeric will also be more of a deep orange. This suggests the red variety might be more efficient at turning nutrients into curcumoids, as this is the origin of the orange hue. Red turmeric powder is mostly yellow turmeric powder reddened due to the addition of iron oxide, and soils high in iron may deepen the yellow/orange hue of the living rhizome. In India, the red powder is often made into a paste with which to anoint the third eye. There are no studies I have seen comparing yellow to red C. longa, but in comparison to C. aromatica, C. longa is significantly higher in curcumoids and antioxidant activity.
celestialroots.com