Baking soda is pure sodium bicarbonate. Baking powder is baking soda with an acid — usually cream of tartar, calcium acid phosphate, sodium aluminum sulfate, or a mixture of the three.
Although baking soda and baking powder are both leavening agents (i.e. cause the dough to rise), the difference lies in how each one reacts to make the dough rise. Baking soda by itself is cannot leaven dough - it requires an acidic ingredient in the dough for a reaction, while baking soda contains its own acidic element. For the same reason, baking powder can be substituted in recipes that call for baking soda, but you cannot substitute baking soda for baking powder.
edit Reaction in Baking
Baking soda is simply sodium bicarbonate; it does not have any leavening capabilities by itself. It is only when baking soda is mixed with an acid such as sour cream, molasses, lemon juice, or buttermilk that these gases are released. Because these gases start forming right after the baking soda and acid are mixed, batters using baking soda should be baked immediately after mixing.
Baking powder is available as single-acting baking powder and as double-acting baking powder. Single-acting powders are activated by moisture, so you must bake recipes which include this product immediately after mixing. Double-acting powders react in two phases and can stand for a while before baking. With double-acting powder, some gas is released at room temperature when the powder is added to dough, but the majority of the gas is released after the temperature of the dough increases in the oven.
This video explains the difference between baking soda and baking powder, and how it affects recipes.
edit Which Recipes use Baking Powder vs Baking Soda?
Whether you bake with baking soda or baking powder depends on the other ingredients in the item you are baking. Baking soda is more common in recipes that have other acidic ingredients like yogurt or buttermilk, say cookies or biscuits. Since baking powder includes an acidifying agent already, it's used in other recipes like cakes and bread.