I've got zero interest in PvP and just want a good single player campaign.
Are you me?
I never had much interest in RTS focused on the multiplayer, so when I do play games in the genre, I play ones with a fun singleplayer campaign.
Also, a question, are you interested in real-time tactics game? Basically, the distinction between RTT and RTS, in a broad sense, is that in the former you don't build a base or units - what you have is what you got, though you can purchase more units or upgrade existing ones inbetween missions.
An obvious place to start would be
Homeworld 1, 2 and Cataclysm. Nothing quite like those games, and the results in one mission carry over into another - meaning all your research, resources and ships. HW1 and Cataclysm are much better than HW2, but all are good fun.
Homeworld: Deserts of Kharak is basically a scaled down prequel to the games above, taking place on the planetary surface, but still retains many of the aspects of previous Homeworld games, as well as campaign continuity.
Another game with a fun campaign is
Dawn of War: Dark Crusade - the singleplayer campaign has a turn-based, strategic layer, and when you attack a province you switch to traditional RTS gameplay, though DoW is an RTS that encourages the player to be aggressive. There's not much carry over from one mission to the next, though you do get to deck your commanding officer in cool gear and can unlock bodyguard units that always spawn at the start of every mission.
Dawn of War: Soulstorm has a campaign very similar to that of Dark Crusade, but the balance is all over the place and the writing is not as good as the previous game.
There's also Winter Assault, where you have two campaigns - Order and Chaos, with different races. There's not much that is unique about the singleplayer in this one.
Original War is a very unique RTS - every unit you control is a unique individual, with different skills and stats, and if they die, they're dead and lost forever. The game also features cRPG-esque choices and consequences that do impact the plot, so it would be more correct to call Original War an RTS/RPG hybrid.
If you're looking for classic Blizzard stuff, since you're mentioning
Starcraft - that's a good place to start, though the campaigns do not really offer anything unique beyond the narrative.
Warcraft 3 is also a fun one to play in singleplayer.
World in Conflict is great - very well-written campaign for an RTS, and it's focused all on the action, there's zero base building.
Warzone 2100 - again, another RTS with campaign continuity. You have a main base that you build up throughout the campaign, units gain veterancy, you can design your own units based off technology you research. It's a bit dated graphically, but still a very fun game to play.
Impossible Creatures - cheesy plup fiction plot where you use the power of science to create horrors beyond human comprehension by combining various animals you encounter into units, each body part changing what your monsters can do.
Battlezone 1 and 2 are first-person RTSes, in the sense that you, the commander, are a unit on the map - you can enter into vehicles and fight the enemy directly or take a hands-off approach and control the battle via the tactical map. Very difficult games, but fun.
Hostile Waters - game has no multiplayer, so the singleplayer campaign is the sole focus. Plot written by Warren Ellis, back when he wasn't a total retard, you are in command of a ship with a nanite factory in it, the Athenaeus, which can build vehicles from metals you recycle. The neat part is that you can outfit each vehicle you make with additional components and different weapons, and then assign a digital copy of a solider that died in battle, or take control of the vehicle yourself.
