I learned most of my lessons on this topic from action and drama films.
I've found that one of the best ways to introduce comic relief in an otherwise tense campaign is to introduce sometimes hilarious events into combat descriptions. For example, let's say one of your party member's gets a critical success on his attack roll and obliterates an enemy. All it takes to add some comic relief then is to throw something like "The guard gives a confused look as the railgun shell comes barreling towards him" or "A growing wet spot appears on his pants as he watches the fireball come closer" to introduce a moment of lessened tension. I particularly like this kind of technique because it doesn't ever distract or detract from the overall mood of the campaign or setting, since combat tends to be a gruesome and serious subject by its nature (and players sense this).
Another great way that I really like is dropping quick one-liners in a tense situation. For example, in a campaign I ran once, my party acquired a spaceship piloted by an NPC. At one point there was some on-board combat, and the party's heavy weapons expert critically failed, making a massive hole in the hull (luckily, they hadn't yet exited the upper atmosphere). The pilot NPC gave the PC an incredulous look and started a slow clap for him, saying, "Way to go, Jim. Way to go." It was a moment of hilarity amidst an otherwise very grim situation, and again, it only added value to the session and atmosphere.
I think there are two keys to successful comedic relief:
- Don't force it; and,
- Make it quick
If you force it, the players will sense it, and it will just be awkward and break the mood (which is exactly what you don't want). Also, if you extend the comedy for too long, it takes over the scenario and your campaign suddenly becomes a silly comedy of errors rather than a drama or action-adventure or whatever else.
Basically, small quips inserted artfully are the key to success.