Apologies in advance for the double-post.
World in Conflict - very well written "Cold War turns hot" story that portrays both sides fairly, characters are basic but are acted and written well, each going through their own story arc.
Legacy of Kain was already mentioned, but I also want to give it a shoutout. All the games have a great story to sit through, but the gameplay leaves a lot to be desired, so your ability to enjoy it depends entirely on how much janky combat and block puzzles you can put up with. Still, it's one of the better narratives that make good use of time travel and has quite a few shocking twists.
Ace Combat 4 is a tragic story written from the perspective of a young boy that is living in a tavern frequented by the enemy ace, framed as a letter he is writing to the protagonist after the war to explain his side of the story, while the protagonist remains faceless and voiceless.
Ace Combat 5 is the weakest in terms of storytelling, with many criticizing the story as being too idealistic, but it has arguably the most epic final mission in the whole franchise. The story is also presented in a far more conventional fashion compared to 4 and 0. I quite liked it, and it's my second favorite after 4.
Ace Combat 0 is presented as a documentary a journalist is filming after the Belkan War (yes, it's Belkan with an e), interviewing various enemy aces that the protagonist faced, with the interesting aspect being that the interviews change depending on how the protagonist behaved, be it as a professional soldier, a noble knight or a ruthless mercenary.
Republic Commando - basically Teenage Mutant Ninja Turtles in space, really does a great job of making you care for your fellow clone squadmates. The overarching story isn't anything to write home about, but the interactions between the four squad members are great.
Indigo Prophecy is 1/3 great story and 2/3 acid trip - the opening few hours are genuinely some of the best paced and directed in gaming, but the narrative eventually just falls apart.
Will probably add a few more if I remember them.