My personal favorite: https://www.youtube.com/watch?v=NJ-xcgBL1mY

While my love for WoW is long gone the Wrath of the Lich King intro is still the first thing the pops into my mind when I hear "awesome game intro": https://www.youtube.com/watch?v=BCr7y4SLhck

The first one that came to my mind was from Anno 1602 (In German because of the iconic voice).

I've also always loved the one from Populous: The Beginning. Here I also like the German voice more, here, it's more dramatic in my opinion.

Probably not the greatest intros ever, but I enjoyed them.
Xenosaga for playstation2 with an archeologist finding a big ancient futuristic artifact then jumps thousands of years into the future in outer space with intro credits.
Civ2 Intro
I was impressed with witcher 2 starting movie.

Definitely SHOGO MAD! Get it here on GOG!


And SHOGO was also easteregged in FEAR.

Ah, the memories. Yes, that was a good intro.

If I were to pick one though, it'd be Fallout.

Ah, yes, and this baby - ANOTHER WORLD.

Played very nice on 486...


UPDATE- 1080 REMAKE

Silent Hill, the music plays a massive part.

A rather interesting (and unusual) example: Sonic the Hedgehog (Sega Genesis). When you turn on the game, you are greeted with the Sega logo (complete with the Sega sound bite, which apparently took something like a quarter of the space on the cartridge), then the title screen (no "press start" or anything like that), then when you press start the game unexpectedly (if it's your first time) starts, which I found to be a bit jarring.

Another even more ridiculous example is a game called Barney's Hide and Seek (Sega Genesis); let the game run on the title screen without any input, and the game will start on its own. In fact, if you don't touch the controller, the game will eventually complete itself (albeit with the worst ending) on its own.

best intro video:
Scratches
the video was only showing a few photographs with piano music playing, but it was perfectly setting the mood for the only game which has ever managed to make me feel uncomfortable in my own home.

honorable mentions:
Arcanum, Planescape Torment, Dark Souls 2 & 3, Witcher 3

best intro without a video:
Torment: Tides of Numenera
lots of well written text :)

Age of Empires II: https://www.youtube.com/watch?v=_rS_n3JVTPE

In case you couldn't tell, I prefer minimalistic intros that get you to the game as quickly as possible.

With that said, one rather interesting intro I've seen is Lufia and the Fortress of Doom. At the start, you control a party of four heroes going into the final dungeon; you fight the final series of bosses right away (though the game gives you an overleveled party), and only after that series of bosses does the game skip forward and introduce you to the real game's main character.

(Note that the end of Lufia 1 isn't identical to the beginning; they did mix things up a little, at least.)

It's worth noting, however, that the intro actually spoiles the ending of the following game in the series; hence, if you play the games in release order, you will know how Lufia 2 ends before you start playing it.

Interstate '76

Full Throttle

Inquisitor

Gabriel Knight 2