That god damn hand in the toilet from Majora's Mask.
Scared the shit out of me as a kid.
The place where you find him is convenient for what most of us probably did when we first saw him.
Also, a LOT of the stuff in Ocarina of Time was scary for an E-rated game. But by Majora's Mask, I was pretty desensitized towards it.
The scariest I've ever been scared in a non-horror game would probably be...believe it or not, Animal Crossing. The one for Gamecube.
If you visit another town via memory card and didn't save, your character would get a Gyroid face. Scared me silly. I cringed every time I booted up the game after that, because your character would always be facing the other way then turn around slowly when you start playing.
It was creepy because basically your character had hollowed out eyes and a mouth constantly shaped in a horrified gasp.