I always liked the intro to Another World, or Out of this World as it was known on the SNES, this video has a funny commentary, viewer discretion is advised etc etc:
http://www.youtube.com/watch?v=r_3tLULZ6Aw
Might not seem very impressive, but when it first came out it was the classiest intro ever, the use of polygons provided very smooth animation, we only really had 2D intros before that, some were nicely done (like the Bullfrog games for instance), but this was just very classy compared to the usual jerky 2D ST game.
I have to agree about Half Life and Battlefield 1942, both very cool intros, I mean the Half Life intro goes on for ages but it's fascinating, totally blew my mind when I first saw it.