This is related to the concept of utility of outcomes, as well as the idea of hedging in gambling. When you have a favorite sports team that you want to win, it's very similar to implicitly placing an emotional "bet" - if your team wins, you gain positive utility, while if they lose, you get negative utility. By betting against your desired outcome, you are hedging the bet, which is a way of betting on both outcomes such that you can guarantee a profit, or at least minimize your losses.
By betting for your favorite team, you have an all-or-nothing outcome - you'll either be happy with the outcome and win cash (both good), or be unhappy with the outcome and lose cash (both bad). By betting against your favorite team and hedging your bet, you'll have a more moderate outcome no matter what - you'll either be unhappy but win cash, or be happy and lose cash. Whether hedging a bet is worthwhile will come down to your level of risk aversion, as well as the utility you assign to the cash, a win, or a loss.