Morrowind and ultima 7 (both parts).

Just go and play Morrowind.

Arx Fatalis (atmosphere, free exploration)
VtM Bloodlines (atmosphere, free exploration, storytelling)
Planescape: Torment (atmosphere, storytelling)
Prophet (custom campaign for Neverwinter Nights, storytelling)

Enclave and Gothic are not bad either, regarding the atmosphere. And, even though it might sound strange for such a static and essentially harmless game, Dark Fall - The Journal still managed to make my spine tingle occasionally when I played it in the dark.

Most immersive game I didn't play: Amnesia - The Dark Descent (just watched people shit their pants on youtube). ;)

Super Metroid
Dragon Age Origins
Planescape Torment
Terraria

I'm sure there are more but those I felt like mentioning.

I'd have to say Amnesia. Scared the shit out of me. You know, the feeling when you get scared after you have held your breath for 5 minutes.

Heavy Rain.I totally identified with character's emotions.

Gonna have to agree with the guys who said S.T.A.L.K.E.R. Top notch atmosphere.

I probably played S.T.A.L.K.E.R. wrong, since I didn't get the atmosphere. For me, the most immersive games have probably been Planescape Torment and Heroes of Might & Magic IV with their awesome walls of text. At least I can't think of any other games I've been as immersed with.

I felt truly immersed in Elder Scrolls III: Morrowind (the only ES game I've ever enjoyed).
Like the above poster, Heavy Rain is truly a character-driven experience that makes you feel involved in their real (mortal) lives; I'd count that game as well.
These days I need to immerse myself into the several unfinished, console RPGs I have laying about.

any adventure game that I've played,the Infinity Engine games,Skyrim,Daggerfall,Saints Row 2,Metal Gear Solid series,Assassin's Creed 2 and Wing Commander 3

Stalker yes. The guitars, the environment, the russian voices (i don't speak russian), the autonomous wildlife, the melancholy, the zone. Did it for me.

Penumbra. Got me in the guts.

Nosferatu. Too immersive.

Aces of the Deep. Puns aside. I tend to forget that I have oxygen available around me, when I play this game.

FEAR. Enough to jump when Alma shows up.

Far Cry. Enough to dream about running on bright green hills and dive into blue clear water from insane heights.

System Shock 2. Enough to refuse getting onto the Rickenbacker, and to develop a psychotic real-life hatred for Shodan. I hate shodan. I hate shodan. I hate, I hate shodan. I hate shodan.

Aliens versus Predator. Enough to refuse stepping into the derelict.

Homeworld. I want to go back, and never leave that game again : a mere screenshot is immersive enough to hook you on your screen for days, staring at it, getting lost in it.

Elite. It used to be. I don't know if it still is. Never felt this with later space games, but maybe wouldn't with Elite either, nowadays.

Subwar 2050. Shut up, I think I've spotted a whale. That is beautiful.

Morrowind (especially during a storm)

Silent Hill 2 - I felt sheer naked terror just walking down the path right at the start, and it never eased off from there.

System Shock 2 - again, freaky creepy scary immersion. Hiding in a crate, listening to the last words of a dead crewman, all the time hearing something unspeakable pace back and forth along the corridor - to this day, just thinking about it gives me the chills.

Persona 3 FES - lose yourself in a year of school life: making friends, falling in love, attending class, studying, sitting on the Student Council, supporting your classmates, going on Summer vacation.... and by night, kick extra-dimensional demon ass!

Baldur's Gate
Metal Gear Solid
RollerCoaster


I know it's a weird list, but i really kinda disappeared into these for weeks at a time when i was younger.

I tend to view atmosphere and immersion as almost the same thing but however you look at it most of my favorite games tend has both in ample amount.

I've excluded MMOs since they are less and less about exploration, atmosphere and immersion these days.

Here is my list:
Super Metroid
Super Castlevania IV
Alone in the Dark
Hexen
Stalker
Demon Souls
Dark Souls
Dead Space
Amnesia: The Dark Descent
The Elder Scrolls series

Kotor
Mass Effect (1)
Gothic (1 and 2)
Morrowind