I have an older model of that type of Canon (mine's the SD400 5MP one), and I think the photo quality is pretty good (you can see some of my photo contest entries for an idea of what it does...even with a horrible photographer using it

). I usually leave the digital zoom off and stick with the optical zoom. I'm happy with it as a point-and-shoot, and especially like the size (I actually use it because I can slip it in my pocket or purse when I'm heading out somewhere, so I have it when I want it...anything bigger and I'd tend to leave it home when I don't want to be lugging a camera around). I think the point-and-shoot camera that Zz uses is the 7MP version of the same camera, so again, for comparison, you can look at some of his entries in the photo contests and see what the photo quality is like (those are good representations since they aren't Photoshopped to enhance anything)...and he's certainly a better photographer than me.
Edit: As for what's better about the more expensive ones, look at larkspur's photo entries.

That's what you can do with a really fancy, expensive camera...if you know how to use it right (it would be wasted on me). If you're mostly interested in just taking snapshots for the sake of memories, and not so much for artistic photography, even 8MP is more than you need. You need the fancier cameras if you want the kinds of optics that will give you good resolution and good depth of field for the artistic shots that you might want to enlarge to poster size to frame and hang. Most people don't need that.