Hm.... I'd have to think about it. XenoBlade Chronicles, Lost Odyssey, and the Mass Effect Trilogy for sure for modern games, and I'd definatly say Chrono Trigger, Front Mission, and Metroid Prime for older games.
Front Mission takes the cake, in the very first mission of the game, you loose, and your fiance dies at the end of the mission. Then the rest of the game has this subplot, that your fiance lived, and you're trying to track her down. As you get closer and closer to the end of the game, you get closer to tracking her down, but just so many missions in the game are so sad, and so accuratly portray the consequences of war. At the end of the game, you track her, down, but just a bit too late. She even knew you were coming, but the main villain of the game kills her, and uses her brain matter (which he wanted specifically since she is an advanced Wanzer pilot) to integrate into an advanced combat chip, which he uses on his "ultimate suit" which is the boss of the game. Essentially, not only does Karen (your fiance) die, but the boss of the game is her brain used against you.
XenoBlade Chronicles and Lost Odyssey are also good too, they touch upon the concepts of loss and war too. XenoBlade surprised me with how it started, but having Fiona come back to life, that kinda killed the atmosphere. Lost Odyssey ended better, with two immortals dying, and never coming back.
Metroid Prime probably gave the best emotional experience in a game, with a really well portrayed feeling of isolation, and if you read the lores, you really feel a bit sad at how close you came to finding the Chozo.
Mass Effect and Chrono Trigger are obvious choices, so many decisions which alter the end game. Even if you get to the same end game regardless, the concept of time travel with consequences in Chrono Trigger, and the decisions you make in Mass Effect kinda make you think a bit.
EDIT: I almost forgot, Shadow of the Colossus, wow. I think more people have played this then Front Mission, but Front Mission gave the same punch as SotC, just 5 or 6x, where SotC really only had the two major cinematics.