That's pretty hard to judge. Every Zelda game has a sad moment, in my opinion. A few of them have really got to me, others I just didn't care. I don't think unless you've really made a connection with the characters, it won't leave an impression on you. Majora's Mask and Twilight Princess are two games that have some of the most memorable characters in The Legend of Zelda series, in my opinion. Therefore it would make sense that people consider these two to be some if not the most sad.
For me, there are two Zelda games which have an equal level of sad(ness). Well, they weren't really the entire games themselves(just about every game in Zelda ends on a happy note) but specific scenes within them. Here are my top two.
Majora's Mask - Anju & Kafei: This has to be the most sad quest in The Legend of Zelda series that I've played through as of yet. Majora has cursed Kafei, reverting him into a child just when his wedding is approaching. In shame, he flees to the Laundry Pool of Clock Town, afraid to show Anju what he has become. To summarize, you have to wait through the three days trading letters and items between Anju and Kafei, as the moon grows closer and closer, she is unsure if staying and waiting for Kafei is wise. In the end, she decides to wait until the very end of the Third Day, and is reunited with her to-be husband. This, to me, completely personified the urgency of the Moon crashing down - and made me decide, that I was going to lay the smack down on Majora. It's rare in a video game, that you actually feel motive due to the surrounding characters. Anyway, this quest was pretty sad, but in an inspiring way. Aside from that, Majora's Mask is literally laced with depression.
Twilight Princess - Ending Sequence: At the end of the game, Midna is forced to return to the Twilight Realm. Forever. It's quite sad to watch and see her and Link must part. My little brother actually cried during this scene, haha, poor guy. Anyway, yeah. Midna was just a really memorable character, and after you have to take her to Zelda(in which Zelda sacrifices her power/life/whatever) I sort of felt a connection to her, just like with Kafei, Anju, and other characters.
