When you add accounts, it adds their address book to the phone if you allow the contacts to sync. Check the "groups" in contacts... you'll see an overall Google group, Yahoo group, and Facebook group. Hence why there are so many duplicates. Whichever one you delete, it'll delete it from that site's contact.
I agree, it'd be nice if it could see that the contacts exist in multiple address books (sites) and auto-merge them in the list... but alas.