Although baking powder and baking soda are both used in baking to help dough rise, they are fundamentally different products.
Baking soda needs acidic ingredients, such as cream of tartar, to release carbon dioxide and cause batter to leaven in a baking recipe. Baking powder, on the other hand, which already contains the acidic ingredients to induce a carbon dioxide producing reaction, doesn't need any other low-pH ingredients to activate.
Baking powder boosts the rising power of baking soda, which is why certain recipes call for both. Additionally, they have different effects on browning and flavor; baking soda helps foods brown better, and baking powder can help maintain tartness in a recipe.
Source: Flickr User Mel B.