By far, the most frightening game i've ever played was Thief 3. If you've ever played this game, you'd probably agree. There was a point where I simply ran out of my room screaming, and I couldn't start the game again for a few days cause I was still frightened. Considering that this isn't a game thats supposed to be scary, this is saying quite a lot.
Under that would be the original Unreal (there were a few spooky moments), and System Shock 2 simply for the fear of being so, so alone in that damned spaceship. The original DOOM also did a number on me back in the day.
DOOM3 was a complete joke. There was nothing scary about it. I haven't played through FEAR yet, but from what I saw in the demo, I was very unimpressed.