Piotr gave a very good explanation of how
sudo works. However, he didn't really motivate why it works this way, so I'll try to add that here.
sudo command was created, we had the
su command. This command allows one user to execute commands as another user, usually
root (as with
sudo, this is the default target user). It was totally undiscriminating, you can execute any command. Since it could be used by any user, was effectively equivalent to logging in as that user, it required you to know the target user's password.
At some point, a little more access control was added: to use
su, you had to be a member of the
wheel group. But since you still could execute any command, it still made sense to require you to know their password.
Requiring users to know each other's, or the superuser's, password was not very secure, though. Often you just want to give certain users limited access to some other account (this is part of a security concept called the principle of least privilege). It also makes accountability difficult: if multiple people know an account's password, and that account is involved in a mistake or abuse, you can't tell which of them actually did it.
sudo was created. Rather than allowing users to execute any command, it has an elaborate configuration file, briefly touched on in Piotr's answer, that specifies precisely who may use it, what users they can switch to, and what commands they're allowed to run. With this fine-grained control over who can do what to whom, we no longer need to give users the target account's password; if we did, they could easily bypass all the controls in the configuration file by logging in as that user. Instead, we normally just require them to prove that they are who they logged in as, by entering their own password -- this is intended to prevent someone from taking advantage of an account if the terminal is left unattended.
This requirement is waived for the superuser -- this account can do almost anything to the system without using
sudo, so it was deemed superfluous. It's also possible to specify in the configuration file that users don't have to enter a password at all -- some organizations use this when they believe the physical security of their workstation environment is sufficient to prevent abuse.