• "Regular" or "plain" paprika
Most of the paprika sold in grocery stores is simply labeled "paprika." Its origins may be Hungarian, Californian, or South American, and it is sometimes mixed with other chiles like cayenne. This paprika tends to be neither sweet nor hot and is a suitable garnish for things like deviled eggs or wherever you want some color.
&bull Hungarian paprikaPaprika is considered the national spice of Hungary and it appears in the country's most celebrated dish, goulash. Hungarian paprika is made from peppers that are harvested and then sorted, toasted, and blended to create different varieties. All Hungarian paprikas have some degree of rich, sweet red pepper flavor, but they range in pungency and heat. The eight grades of Hungarian paprika are különleges ("special quality"; mild and most vibrant red), csípősmentes csemege (delicate and mild), csemege paprika (similar to the previous but more pungent), csípős csemege (even more pungent), édesnemes ("noble sweet"; slightly pungent and bright red), félédes (semi-sweet with medium pungency), rózsa (mildly pungent and pale red), and erős (hottest and light brown to orange). In the US, what is marketed as Hungarian sweet paprika is usually the édesnemes variety.
• Spanish paprika or pimentónAlthough generally less intense that Hungarian paprika, Spanish paprika can range from dulce (sweet and mild) to agridulce (bittersweet and medium hot) to picante (hot), depending on the type of peppers used (round or long), whether the seeds are removed, and how they are processed. In Spain's La Vera region, farmers harvest and dry the chiles over wood fires, creating smoked paprika or pimentón de La Vera. Smoked paprika should be used in paella and dishes where you want a deep, woodsy flavor.
If you have a recipe that calls for paprika without specifying which kind, you can usually get by with using Hungarian sweet paprika. But also consider what type of color, sweetness, pungency, or heat you'd like to add and experiment with the wide world of paprika varieties!