Also, the end of Brood War was crushing. What was done to DuGalle, Stukov, Raynor, Mengsk, and most of all, Zeratul. Definitely Zeratul.
Bonus missions from the N64 version are canon by Word of God, and thus Stukov Lives! After being infested, then de-infested, and left a broken half-shell of a man. But he lives!
Or the part where Arcturus abandons Kerrigan to the Zerg. Especially in the novelization, after Raynor decides to evacuate a group of survivors and civilians rather than keep looking for her.
This is made even worse via a cinematic in StarCraft II, where we see Kerrigan, the last person left at New Gettysburg, calling for backup. She's implied to have watched her entire force be devoured by the Zerg, and she's darting from place to place, fighting off zerglings and hydralisks erupting from every shadow, as her cloaking device runs out of energy and her ammunition runs out. Down to just her sidearm, she sees a mass of zerglings coming towards her, and knows from the lack of radio contact and the mutalisks wheeling overhead that her rescue is not coming. Insteadof fighting to the last, she drops her gun, completely broken, and juststands thereas the Zerg wave rushes her. If this wasn't Arcturus's Moral Event Horizon moment before, it certainly is now.
Or, just this:
Raynor: Sarah? Is that really you?
Infested Kerrigan: It was.
Bear in mind the reason for Arcturus' betrayal in the first place: Kerrigan was one of the three Ghosts who murdered his family for resisting the Confederacy. He only survived because he'd enlisted in the Confederacy Marine Corp. He willfully spent years working with one of the three who murdered his mother, father, and little sister...because she could help him topple the Confederacy. That smug smile he has during the cinematic depiction in the sequel of his original betrayal suddenly makes more sense: he didn't just kill her. He knew about her and Raynor. He let her fall in love, and then killed her. Just remember: they couldn't even find his father's head. That explains an awful lot about Arcturus Mengsk.
Pylo the Pylon...
The entirety of the Refugee mission chain in Wings Of Liberty, involving the Koprulu Sector's unluckiest bunch of colonists and your efforts to save them from the Zerg - up to and including picking a fight with a Protoss purification fleet if you choose to do so. At the end, if you chose to protect them, their leader leaves the ship, thanking Raynor profusely for giving the colony a second chance. If you chose to help cleanse the infestation instead, she winds up infested as well, and Raynor is forced to kill her.
The semi-secret Starcraft II mission "In Utter Darkness", which is a playable peek into a Bad Futurethat will result if Kerrigan is killed. Basically, without Kerrigan to guide them, the Void Daemons took control of the Zerg. Humanity is extinct, and you get to play as the last Protoss force in existence, holding Zerg off until the Templars can seal away a record of the conflict, and then the mission continues with the objective "Fight until the last Protoss is killed". Guiding the Protoss in a final, glorious, doomed Last Stand, you get to watch as the last vestiges of the species, including every named Protoss in the game, are killed, all while they lament the fact that they killed Kerrigan and thus allowed this horror to happen.
The ending of Wing's of Liberty, for untraditional reasons. After all the pain and suffering Raynor has endured for four years, he has finally managed to make a real difference for the better in the galaxy, and he managed to save the woman he loved from the zerg, and in doing so can now finally forgive himself for his failures. Even though there is a long way to go, the fact that Raynor walked away with a real victory for the first time in three games is pretty damn touching.
After shooting his oldest living friend in the face. Tychus's pointless death was what brought out the tears for me.
Fenix's death in Brood War. Especially considering that she killed him with broodlings. That just felt too cruel.
And the worst part? Neither it, nor the entire zerg-protoss war, was supposed to happen. The zerg, while they are normally a fairly hostile and paranoid species, are not naturally cruel or sadistic, and don't need to overwrite the personality of the infested to make them as heartless as Queen of Blades Kerrigan. It was all because of a single Xel'Naga's selfishness and greed.