Zelda games especially Majora's Mask

Agree on all counts.

Atmosphere = fallout new vegas. That is all...

Dark Souls and Fallout.

Xenoblade, OoT, Suikoden II, Metroid Prime, Majora's Mask, Final Fantasy VII, Silent Hill 1 and 3, Fatal Frame Crimson Butterfly and believe it or not Luigi's Mansion Dark Moon.

Ratchet & Clank Going Commando, Heavy Rain, Max Payne 2, Mass Effect 3... these are some I can come up with off the top of my head.

I think when people are asked this question they often gravitate towards horror games but I appreciate any game that crafts a consistent, compelling, 'believable' environment that doesn't constantly signpost that you are actually playing a game. A recent failure in this regard for me was Assassin's Creed 4. I wanted to get more absorbed in its, admittedly interesting and compelling game world, but it seemed determined to pull me out every few minutes.

My votes, off the top of my head:

Metroid Prime (Trilogy)
Half Life 2
Deus Ex: Human Revolution
Resident Evil 4
OOT and Majora's Mask
Rare games generally (particularly Goldeneye, Perfect Dark and the Banjo Games)

Dead Space 1 and 2, Bioshock (Infinite>1), and Shadow of the Colossus are probably #1.
Others: MGS4, Portal, RE4, Outlast

Seemingly-amazing-atmosphere-when-originally-played-in-2008-but-near-laughable-atmosphere-when-played-in-2014 AWARD goes to.......Resistance: FoM

I would probably say you're talking shit if I hadn't played it for myself. I played it for a couple hours and put it back on my shelf.  I really thought i'd get into it too. Maybe i'll get back to it.

Dragon Quest
FF9
FF12
Tales of Vesperia
FF6