Hard to choose.
You see there are various aspects that play importance in an intro sequence,and there are some games that make one or more aspects better than the other,but its not always the same aspects,so I have more than 1 games that I find them having the 'best' intros.
Here is a list of them,and I also explain the reasons I find them to be on the 'best' list.
Super Mario 64: That is the single intro that had me most excited than any other game. Wasn't that much of a deal story-wise,so the excitement came mostly from technology showoff. I mean a REAL 3d game,and not pseudo-3d like in the past! The world looked so real. The sky was bluer than ever,the sun was shining more than ever,the Princess had actual voice you could hear when she spoke,the water had actual waves,there was depth,waterfall had its own sound effect of water falling and you COULD EVEN HEAR ITS VOLUME GETTING HIGHER AS YOU GOT CLOSER TO IT!!!!! These things seem trivial and standard now,but they didn't existed before Mario 64 and so they made your jaw drop on the floor and your eyes to open wide when you first played it. You could even use the camera for the first time in gaming history!!! That's right. The term "camera" that we use in all 3d games these days came from Mario 64,because there was a cameraman following Mario in that game. The technology jump from SNES to N64 was huge. It surprised very much.
The Legend of Zelda Ocarina of Time: Perhaps the most 'complete' intro sequence I've experienced. You learn who the player's character is,which are his friends,how to play the game,even how the world was created,in a nicely pacing intro. You learn all the stuff you would need to learn to play the game and know a few things about people and places,and all these paced nicely,in a convenient way.
Mass Effect 2: That's the most sentimentally touching intro sequence. It doesn't develop the characters enough nor there is a striking difference regarding gameplay or technology in comparison with Mass Effect 1 or 2,but if you previously played ME1,then you already know more than enough to know the characters and care for them,and seeing these things happening in the intro makes this game's intro the one that made me feel feelings for its characters more than any other.
Halo (the original): That's the first time I saw a straight forward tutorial that didn't felt intrusive neither stretched out. Dramatic and Epic,gives you an idea of the world and what's happening without telling you everything though and thus keeping some mystery.
