In addition to sweet and hot paprika, there is also smoked paprika, which is made from peppers that have been smoked over oak fires. This process gives the paprika a distinct smoky flavor, adding depth and complexity to dishes.
When substituting, use common sense. Cayenne pepper is as much as 50 times hotter than sweet paprika, so a 1:1 substitution wouldn't work unless you want some heat—the right substitution, in this case, would be 1/3 of a teaspoon of cayenne per 1 teaspoon of paprika. Make an informed decision, but also use thorough recipes that might suggest reasonable substitutions.