I'd have to go with the first Max Payne. That game just oozes atmosphere. It had the perfect soundtracks to go along with Max's depressing tone and makes you feel really sorry for him.

Second would probably go to the KH games. They're bitter sweet and know how to hit you in the feels.

What are yours? (Note: It doesn't have to be depressing, just as long as the game is captivating and has a strong tone.)

Metro games do an amazing job at captivating the post-apocaliptic, desolated atmosphere.

Forgot about that one.

Super Metroid, Metroid Prime and Half Life come to mind, they're soaking in rich environments and atmospheres. Especially Ravenholm, Lower Norfair and Phendrana Drifts.

Love Max Payne :)

I would probably go with Silent Hill. It's heavy with a pervasive sense of dread. Others would include Super Metroid, BioShock, and Ico.

I think Super Metroid, Portal 2 and The Last of Us take the cake. Ni no Kuni could also be a contender depending on your definition of "atmosphere". There are probably other games I'm forgetting.

Alan Wake.

Metroid Prime Trilogie.

Atmosphere is my favorite thing in a game. I love getting absorbed into the world. Silent Hill: Shattered Memories, Okami, Portal 2, Xenoblade, Bioshock Infinite, Metroid Prime, Fragile Dreams, and most Zeldas are the first things that come to mind from recent history.

The atmosphere in Silent Hill games was amazing.  You just knew bad stuff was waiting for you.  ICO was also fantastic.  Such a strong feeling of isolation, of being abandoned and on your own in a massive and decaying castle.

I think Persona games have a very interesting vibe.  The idea that under the ordinary world something sinister is seething, I like that a lot.  It's so creepy because it's framed by the ordinary.