Plots as a whole not so much, but each also has their own little moments of sadness, joy, or profundity.
Take the end of The WindWaker, for instance. First it gets you to sympathize somewhat with Ganondorf and see a glimpse of a battered man who knows he's far gone from where and why he started, then you hear him laugh in sheer villainous joy.
Metroid Prime series tells sad stories of doomed civilizations in each game (the Chozo colony on Tallon IV, the Luminoth and their slow defeat by the Ing, the Bryyonians and the Elysians in Prime 3).
Other Zelda games offer glimpses of triumph or desperation.
Fire Emblem: PoR and RD told resonating stories about racism and the ties of religion and political oppression.