Didnt see it mentioned yet but Prey had an amazing atmosphere. I was completely sucked into the alien space ship design.

Easily Bioshock but the realism of Call of Duty 4 is really something.

Another vote for Condemned. The first six or so chapters, while the story was still mysterious and before it became laughable, were seriously unnerving. The department store with its mannequins and muzak, the library with its rows of books and...shit, what just moved in that corner? I had trouble sleeping because of that game.

The original Ghost Recon raised my blood pressure quite a bit. One of the few games I could never play with headphones; it made me distrust my surroundings.

Here are couple of my favorites...

Super Metroid:
We pretty much have the definition of atmosphere right here. Graphics, music and gameplay all come together to perfection.

<object width="425" height="355"><param name="movie" value="http://www.youtube.com/v/7Zu6iyPP0JU"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/7Zu6iyPP0JU" type="application/x-shockwave-flash" wmode="transparent" width="425" height="355"></embed></object>

F-Zero GX:
Sega does an amazing graphical job here, giving the game some insane atmospheric track designs. I wish Nintendo would hire them again for a Wii version.

<object width="425" height="355"><param name="movie" value="http://www.youtube.com/v/Orp3HSjBlls"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/Orp3HSjBlls" type="application/x-shockwave-flash" wmode="transparent" width="425" height="355"></embed></object>

Psychonauts had a pretty unique atmosphere.

Fatal Frame really creeped me out. That was a couple years ago, before the J-horror stuff became a bad cliché. But this game gave you the feeling of being in a movie like Ringu or Ju-on.

<object width="425" height="355"><param name="movie" value="http://www.youtube.com/v/u8Uemuj5Gew"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/u8Uemuj5Gew" type="application/x-shockwave-flash" wmode="transparent" width="425" height="355"></embed></object>

Fallout!

My god, if there were ever an award for consequence beyond the scope of a 'game' this wins. A post apocalyptic world, etc, shanty tows and intense radiation with mutants and anarchy etc. But you could do anything, and the game would let you! Put your intelligence to 0 and you can only grunt at people and they'll shoot you, you can do it! Drive around in your stolen car until you come to some slavers. Any other game and they'd be bad guys you kill for money, and there's no exception here, they're loaded. But why are they bad? Befriend them and you can become a slaver. Hell, you can even sell them your wife for $1000 (hey, its the apocalypse, the dollar is devalued to a bottlecap..) Become a Gangster, a slaver, a pornstar, an assassin, all or none. You could do anything and it was mostly hilarious. Roll on fallout 3

Ico

Both Ultima Online and EverQuest really sucked me in during their beta tests.

KOTOR, Dark Age of Camelot, Star Wars Battlefront II

System Shock 2

Crackdown - it has all the major urban area locales idealized, and comes off as a fun place to hang out in, violent gangs and all.

But I think Oblivion is my favorite "world" - and one of the only games where it's fun to just wander around aimlessly.

Speaking of which, in GTA: San Andreas, it was fun to tune the car-radio to the country station, and just cruise around the non-urban, countryside/hills areas of the game - that felt really authentic to me.

<object width="425" height="373"><param name="movie" value="http://www.youtube.com/v/ZgDEh3UN404&border=1"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/ZgDEh3UN404&border=1" type="application/x-shockwave-flash" wmode="transparent" width="425" height="373"></embed></object>

Half-Life 2
Bioshock
Oblivion
COD 2 and 4

GTA III
GTA: Vice City
GTA: San Andreas

Metal Gear Solid 3 and Shadow of the Colossus the best experiences in the history of gaming period.

Assassin's Creed - I just loved the Middle Ages and the Crusade. The cities are beautiful and being able to climb up to the towers was awesome.

For me....

early Resident Evil games (the first one especially)
early Tomb raider games
Elder Scrolls 4: Oblivion
Star Wars Knights of the Old Republic 1 and 2
The Legend of Zelda: Ocarina of Time
GTA3 and GTA Vice City (I didnt really play San Andreas)

Honorable mention - Freedom Fighters, Mass Effect, Assassin's Creed

Im sure Im forgetting some.

Shenmue I

You really start to feel like a member of the small community there.

Good call on Shenmue, that one is definately up there.

Lost Planet

signed -GiantRobo

Halo 3

And if you disagree it is because you are a pathetic low level and have no clue what you are talking about.

signed -Vampyre (or whatever his name was)

Thief
STALKER
Deus Ex

bite me Wii Boy

I agree with a lot of others here, Shenmue especially.

I'm going to go ahead and mention something more old school. A jewel from my childhood past:

X-Com

I don't know how well it holds up today, but at the time, that game captured my imagination like no other.