The first two Xenoblade Chronicles games managed to invent alien fantasy worlds that are unmatched in terms of presenting you to an actual unique world to explore and the music does accompany that feeling perfectly.

Super Metroid and Demon's Crest. Nothing has come close to what these two achieved in terms of athmosphere. The first Diablo and Bloodborne would be up there in the TOP 5, but still far behind the 2 Super NES gems.

I don't like excluding games because of their stories, because I think stories have great power for creating atmosphere. I really liked the desperate atmosphere of Final Fantasy XIII when I first played it, although I guess a part of the atmosphere was also coming from the lore, largely told via text excerpts. Final Fantasy X is another game where I feel like atmosphere was largely created by the story.

But if we're excludies stories, there are still some worth mentioning. Ico comes to mind. I still haven't finished that game, but I remember it having a really strong atmosphere. I look forward to finishing it one day, but I don't know when that's going to be in practice. Oblivion felt absolutely magical with its open world than in the day, although a part of that might have been how new it was to me at the time. Since then, open worlds have become much more common. That said, I still suspect Oblivion genuinely had its magic as well. That sense of exploration and a living world just were something else ands probably still are there to an extent.

Othern than those, I'm not sure there's much worth mentioning among the games I'm familiar with. Based on my experience, strong atmosphere, at least without a strong connection to the story, just isn't all that common in games.

The Metroid Prime games are especially good at this; they really immerse the player in this lonely and hostile alien worlds that drip with mood.

Condemned: Criminal Origins is also a game where the grimy visuals and sound designs create an atmosphere so thick you could cut it with a knife.

WoW is great pick, I would probably add that to my list as well. I've never thought much about the atmosphere in W3, but maybe that's just cause I've played it too much :P

Thief - The Dark Project (1998) had 'atmosphere so thick you could cut it with a knife'.

This is a tough one, because there are so many different kinds of atmosphere, from cozy to scary to melancholy to even bleak and whatnot.
Most Sony story games are thick with the stuff, but there are so many others.
The new FF7 games for example really do it for me, the excellent music does a lot of heavy lifting there. Or even the lack of music sometimes. In Remake, when you need to leave Aerith's house in the middle of the night without saying goodbye, there's no music, just the sounds of a wind. You really feel alone out there in the darkness and miss her lively presence. Then you find her again and the music comes back, creating an adventurous vibe that I also love.

It's still my highest rated game, The Witcher 3. Even when I'm depressed as a pancake I can feel the vibes come through from memory, there isn't many moments in that game that don't have at least some ambience and vibes. Listening to the OST alone is a vibe. Even thinking about it now I can feel Ard Skelig and the busstle of Novigrad, it cuts so deep, I fucking love it so much I can't explain in words. Thanks for this thread, I'm gonna start my long held off Next Gen patch run of this game.

Oh and while I don't think the game is very good, my second would be Hollow Knight. Third most likely Transistor for vibes alone but that game is actually quite fantastic too. 

Super Metroid is like a king. The theme music when you arrive in Maridia or Wrecked Ship is really good.

It gotta be OG fallout (1 & 2) and Diablo. Also obligatory Witcher 3 shoutout