So many games fall into this category but the best goes to Metroid series:

The best is metroid prime and super metroid heck listen to this from super metroid:

http://www.youtube.com/watch?v=mtLWUkAqQBM

You think that best atmosphere in a game is one in Super Metroid...? You haven't played many games have you..?

PONG!
I kid. Seriously...

For 'spook' factor I'd like to throw an Indy out there, the Punumbra Collection. Some of the better known horror games can look good but Penumbra does a nice job with less is more. It's so simple and bare bones (pardon the pun) at times it can be very eery.

Alas the games that 'pull me in' the most effectivly are the MOH games. They aren't my favorite games out there, and there are SO MANY WW2 shooters, but I have to confess I've been known to flinch and duck while playing them. Put on the surround sound and I promise you will actually look over your shoulder when you hear something behind you.

S.T.A.L.K.E.R: Shadow of Chernobyl, hands down.

Definately

I really enjoyed Lone Survivor mostly because of its atmosphere.

There is no need to be rude.

Hey Fallout 1 is also a contender.

I've played a lot of games in my days and I still think Super Metroid is one of a kind. And you don't need to bring nostalgia in because the first time I finished it was on a SNES emulator long after its original release.

Just try to play Silent Hill 3 at night, lights out.

I did and it was eerie :)

no one is mentioning Amnesia? It was really 'atmospheric' when I played it.

Metro 2033 was quite atmospheric when playing: I had always the idea that suddenly something could jump at me, especially outside. And it looked cold at times, very cold.

Anyone ever try to crain your neck and peer around a corner while playing any of these? I have!

Anyone ever dream in Tetris? I have!

S.T.A.L.K.E.R. SoC is right there with the best, imo.