The only moment I actually got a "sad" feeling while playing Zelda, was the end of Twilight Princess. First, let's think about the other possible sad moments here...
The whole Anju and Kafei scenario was obviously dramatic. Both are in love. They make masks symbolizing their engagement to one another, to later be combined into one mask once they are married. Then, out of no where, Kafei gets turned into a kid. Now, he is obviously sad about this, but Anju has no idea really what's going on. She's only sad because Kafei is missing, but we know he's still alive, so that takes away from the sadness that would be "Kafei is dead". Alternatively, he's just been turned into a kid. Its not like he is actually dead, so there's always the potential for him to turn back into an adult, which is apparently what happens as the two are shown to be getting married in the credits of the game. So, conclusively, the scenario is dramatic, but it has a happy ending which makes the whole thing a lot less, if at all, sad to me.
I'm a big fan of Link's Awakening, but Koholint Island disappearing isn't really sad to me. The fact that Marin is obviously gone could be considered sad, since there is an obvious love interest between her and Link (well, more-so from her towards Link). But in the end, it was all a dream world. Marin never really existed outside of this dream world, so what is there to really be sad about? I could have a dream tonight about the greatest woman I've ever met, but once I wake up, I know it wasn't real. It might be sad that she wasn't real, but this just doesn't stack up well against other potential "real" moments in the Zelda series.
A Link to the Past was filled with what could be considered sad moments, such as Link's uncle dying, the King dying, the Flute Boy disappearing into the influence of the Dark World... There are many there that I think could be considered top of the list. But ALttP has always carried a sort of emotionless feel to it for me. Link has little to no emotion in the game itself, and when the player is experiencing the game through his perspective, it makes other things happening less personal, and less sad.
Mikau's Death in Majora's Mask wasn't very sad at all. Before he flops over dead, he perks up just fine to play a tune on his guitar. The whole scene is really surrounded by less than serious actions and dialogue.
There are probably many I'm leaving out, but I'll end it with the one that I picked. Throughout Twilight Princess, there really isn't a whole lot of Link showing emotion or attachment towards Midna, at least until towards the end. Finally, when Midna's true form is revealed, you understand that she was struggling the entire time be being sealed in the form of an imp, though she never once really let this get to her. She kept to her goal in helping Link save Hyrule and keeping the Twilight Realm from falling deeper into the hands of evil itself. In the end, we find out what she really looks like and who she really is. The last scene clearly shows that Link cares about Midna, and Midna obviously has grown to care about Link. Then, she shatters the mirror with her tears, making it impossible for the two to ever see each other again. Unlike in most other Zelda games, this whole scenario is based on Link's emotion and a character directly connected to him. It is more personal because it has to do with the character you are actually playing and experiencing the game through's eyes. Unlike "Link's Awakening", this attachment actually exists in the real world, so its not like it was a fake attachment to begin with. Knowing this, I conclude that this is the saddest moment in any Zelda game to date.