Upss! Nice question guy. I will try to reply you as well as I can, ok?
Well there are basically 2 sorts of multiplayer games: 1) those that you can play throught the net. 2) All those console games that you can play with a friend or friends using more than 1 controller.
You´re interested, as I can see, in the 2nd type. These games are ready to play with any other person with another controller. You´ve many sport titles: Madden, Fifa, NHL... and all those. Then you´ve others like GT3, MotoGP, Tekken (and all fighter ones...) and in a bit of time you´ll have a great multiplayer game: Rayman M. It´s ready with many mini-games that you´ll enjoy with a friend or friends, up to 4.
If you´ve got more questions, please, feel free to ask.