Since you asked about fighters I'll stay away from discussing the use of auto-cannons vs ground targets. Keep in mind that larger-caliber auto-cannon were often intended as "tank buster" tactical air support.

With regards to anti-air weapons, the tradeoff in general was between a smaller lighter weapon (MG) with high ROF and lighter ammo, vs. a usually heavier weapon with heavier shells and lower ROF (cannon). But, the damage with a successful hit was higher with the bigger auto-cannon shells.

Innovation occurred throughout the interwar period and during the war itself.

The difference upon impact was not due only to the size of the projectile but its design. A purely kinetic shell might penetrate the aircraft giving only a hole, unless it hit a crucial spot. Even a larger kinetic projective would make only a slightly larger hole. Many Allied bombers returned shot full of holes and with one or two disabled engines.

The most common type of projectile used in aircraft autocannon was a "mine shell", which combined a thin-walled projectile with a relatively large amount of explosives. An explosive shell would likely do far more damage; for example defeated and igniting "self-sealing" fuel tanks, removing fatally large chunks of wings or stabilizers, disabling crew, etc.

Incendiary rounds were also common in autocannon.

The perceived downsides of autocannon were:

1. Compared to an MG, fewer rounds could be carried, being much heavier.

2. The muzzle velocity was often lower, especially for auto-cannon designs modified to be lighter and simpler for aircraft use.

3. Due to the above two factors, it wasn't as easy to hit an enemy, especially a fast-moving maneuvering enemy. Spraying more and faster bullets was better for hitting those targets, even if less damage was done per hit.

4. The weapon itself was often heavier. Weight reduces performance, all other things being equal. Even for aircraft designed as bomber-destroyers where manvuering wasnt a top concern, getting aloft and intercepting the enemy was easier if you carried less weight,

Because of these factors the auto-cannon was often (but not exclusively) used in aircraft especially designed to kill bombers, which were big and slower targets (easier to hit) and required more damage to disable or destroy.

Use of autocannon varied greatly by nations, and innovations continued through the war itself. The USA had only two aircraft with auto-cannon, an early variant of the P-39, and a bomber-destroying variant of the P-38. The Germans made liberal use of them, including both MG and autocannon on the workhorse Bf-109. Some of this was doctrinal, and no doubt some was a reflection of the direction of the war, which saw Germany defending against several years of US/UK campaigns of massed four-engine bomber fleets. Simply put, the Germans had more need of "bomber killing".

One rather odd but effective use of auto-cannons was the Luftwaffe's "Schräge Musik", auto-cannons mounted to fire UPWARDS, and used by flying below a bomber formation, usually at night.