Ocarina of Time
"A long time ago... There was a man in this very village who had an eye they said could see the truth! Now usually, you have to train your mind's eye most strenuously to actually see the truth... But this fella, no, they say he had a different way of doing things... His house stood where the well is now..."
It seems like everybody includes the Shadow Temple on lists like these, and for good reason. However, I feel that it's mini dungeon predecessor has it beat.
When you arrive at Kakariko Village during your adult journey, the entire village is on fire and you see Sheik standing before the well just as Bongo Bongo, the spirit contained within the well, breaks free and goes to the Shadow Temple. You need the Lens of Truth to go after him, but unfortunately the well is sealed off in the Adult timeline, forcing you to go back as a child.
Thus you enter into the creepiest place a Zelda game has offered to date.
The caverns you find yourself look like they may have once contained torture chambers and you even come across a crypt. Many of the enemies you encounter have some affiliation with death, such as Gibdos (Mummies), ReDeads (Zombies), Bubbles (Flying Skulls), etc., and since you're a child they are much harder to deal with. It's also pretty easy to fall into one of the many traps laid around the area.
And the music... Oh man, is it ominous. As usual, I'll add a link at the bottom.
When you discover where you're actually supposed to go, you just can't help but feel accomplished. And then you are met the most disturbing looking enemy in the entire game, the Dead Hand. It's a tough fight with such a horrifying creature, but obtaining the Lens of Truth for defeating him is an awesome reward.
The main reason I picked this over the Shadow Temple (which is also pretty spooky) is because you traverse the well first (they both have some of the same enemies, traps and the same music) so you're pretty used to it by then.