Well, I'm not a horror fan at all (my mind is active enough when it comes to torturing me without added help, thanks) but if I had to pick a couple games...
Metroid Prime Hunters...the opening scene on the Celestial Archives is the only time the game does the mood really well...I remember when I first got the game...I played until I got the message about "confirmed arrival of unidentified aircraft"...knowing it to be one of the hunters, I flipped out and shut the game off for a day.
Metroid Prime 3: Corruption. This one's probably the scariest game I've played. The corpses that jump out at you for a second, then crumble to dust with a ghostly scream when you open some doors? I jump every time. The times where there're so many bodies nearby that you know darn well something powerful is in the area? I start to worry. It's not designed to be a horror game, but it sure as heck freaks me out.
And finally, Legend of Zelda: Link's Awakening DX. I'm kidding here for the most part...but I'll never forget the day I jumped and freaked out bad enough to drop my Game Boy Color when Keapora Gaebora showed up as I was leaving the desert. I have no clue WHY it scared me so bad, but the suddenness of it probably scared me worse than any other game.