They're both white powders. They're both used in baking. Both substances are leavening agents, substances that release gas bubbles and puff up dough so it's fluffy instead of flat. Both have the same look and texture, but they are used differently. What's the chemistry behind that?
Baking Soda and Powder
As everyone knows from early chemistry class, baking soda fizzes over the sides of a jar when it's combined with vinegar. The sodium bicarbonate and the acid in the vinegar swap a few molecules, resulting in carbonic acid. Carbonic acid causes the slightly acidic lemony taste in mineral water, and is present in almost all carbonated beverages. It breaks down quickly into water and carbon dioxide. In soft drinks, that carbon dioxide fizzes to the top. In baked goods it does the same, but more slowly, expanding bubbles all the way through the dough and causing it to rise. It doesn't have the massive reaction that we see in vinegar, because people rarely make, say, vinegar brownies. More often there are weak acids in the baked goods, like chocolate, buttermilk, or honey, and the baking soda fizzes slowly.
Baking powder is another matter. It's baking soda, with cream of tartar is mixed into the soda. When moistened, the two ingredients react more automatically and violently, fluffing up the baked goods right away. In most modern baking powders, there's also a second ingredient, sodium aluminum sulfate. It doesn't react right away, but breaks down into an acid when heated, leading to a second boost of bubbles.
What Happens When You Mix Them Up?
Why does one mess up the other's recipe? First there is the taste. Baking soda is a base, and these have bitter tastes. If they're not carefully mixed in with an acid that can neutralize them, they result in bitter food. A little lemon, honey, or chocolate is all it takes, but baking soda won't work in breads or plain cake. Baking powder, since it comes with its acid, neutralizes itself and is good for breads, pancakes, and cakes that demand no bitter taste. However, if there's going to be acid in something, like buttermilk scones or sour cream cake, a little soda is needed, no matter what, to neutralize the taste.
But why does substituting one for the other result in flat food every time? Well, for one thing, if soda is put in a recipe that doesn't have any acid at all, it's just a bitter powder that does nothing to leaven the dough. For example, if you put baking soda in buttermilk pancakes, but substitute milk for the buttermilk, the pancakes will be flat and bitter. (This will also happen if there is too much soda or acid. The reaction will be too violent and deflate soon after fluffing.)
Baking powder doesn't have that problem; it puffs up no matter what. However, just like carbonated beverages get flat if they're left out too long, baked goods with baking powder lose their ability to bubble if they're left to stand, and have to be baked immediately. To return to pancakes, plenty of people have started out a batch of pancakes getting big fluffy cakes. After a while, though, the pancakes collapse and the middles get soggy. The powder has fluffed up the suckers, and it has collapsed. Even the heating won't help if the dough has missed out on the first set of bubbles. Even under perfect conditions, it doesn't produce enough bubbles to puff up a really dense dough.
Basically, cooking has a lot of experimental chemistry involved. Every recipe needs to have its acids balanced out, its bases accounted for, its viscosity measured, and all its reactions timed. Substituting a leavening agent usually ends in disaster. And bad brownies.