From the manual:
Each new level of Spying & Intelligence research increases your own research bonus by 1% and decreases your opponent’s bonus by 1%.
Spying & Intelligence levels only compete with each other, so if the enemy have higher intelligence than you, the only net effect will be to negate the benefit you’ll get from your own intelligence level. It won’t prevent or slow the base research % chance per turn.
So if UK has lvl 1, and Germany has lvl 0, then UK research gets 1% increase and Germany gets no decrease
If UK has lvl 1 and Germany has lvl 1, then both get no increase or decrease, as both levels cancel each other out.
If UK has lvl 2 and Germany has lvl 1, then UK research gets 1% increase and Germany's 1% bonus is cancelled.
If UK has lvl 2 and Germany has lvl 0, then UK research gets 2% increase and Germany gets no decrease
If UK has lvl 3 and Germany has lvl 1, then UK research gets 2% increase and Germany's 1% bonus is cancelled.
and vice versa.
So, If Germany has lvl 3 and UK has lvl 1, then Germany's research gets 2% increase and UK's 1% bonus is cancelled.
Only the highest Spying & Intelligence level belonging to a fully mobilized enemy Major is taken into account, and compared with your own Major’s level. Friendly Majors’ levels do not assist in this respect.
Thus, if in that situation Germany declares war on the US, which also has lvl 3, then the US lvl competes with that of Germany directly, so Germany's lvl 3 will cancel out US lvl 3 and vice versa.
Only one major in the enemy alliance is counted. Thus, for the other majors, it doesn't really matter whether you are at lvl 0 or at lvl 1 or lvl 2 if your enemy is higher or already maxed out at lvl 3.
That is why generally, you only need one major in your alliance to research S&I (typically UK for allies and Germany for Axis). However, neutrals don't compete with anyone yet, and hence, they can get the full bonus for as long as they are neutral. For this reason, it's very good to do for the US, as they research a lot and they are neutral typically the longest. It would be good for USSR as well, but usually they don't have enough resources and they aren't neutral long enough for it to really pay off.
For Japan, whose only enemy at first is China, it's also ok to get S&I except typically you'll reach your cap too fast, so you might as well research something else directly.
I typically do lvl 3 for the UK because I double chit, and I just let the last chit run out, but generally, the US will join with lvl 3 so UK can stop at lvl 2. The reason for the UK to get lvl 2 is mainly to make sure the Germans don't power ahead in research.
Spying & Intelligence will also increase the chance of a research breakthrough, so there is a lot to be gained from researching this category
So this is the main reason to still get lvl up in S&I even though you already cancelled out the research bonus of the enemy alliance. But it's only really important for nations that research enough for it to pay off. This is generally only the UK & US for Allies, and Germany for Axis. Again, it would be worth it for the USSR, but they tend to be too poor to afford it. Japan is kinda debatable, but they also tend to be too poor to afford enough research for it to be worth it after they are at war with the US.
TLDR:
Only get S&I with UK, US & Germany.