Caleb has a post up on the .38 Super for self-defense.
The obvious comparison is to .357SIG, which is a fine and reliable cartridge in guns designed around the shorter 9mm/.40 platform, but is a bear to get running in a 1911 action, which is designed around a longer cartridge and has a two-piece feed ramp in its unmodified form.
The .38 Super is merely a higher-pressure loading of the old Colt .38 Automatic round, which dates back to some of John Browning's earliest autopistol work, 'way back in 1897; the two cartridges are dimensionally identical (which is why all .38 Super is technically "+P" according to SAAMI.) The advantages it has over the .357SIG are fairly esoteric and mostly center around the bottleneck: The short neck makes the SIG cartridge difficult to reload, and limits the range of bullet weights the cartridge can handle easily.
I've always hoped for a .38 Super revival, even if one attempted resurrection in rimless form has already failed (the "9x23 Winchester".) The reason is that the longer case can handle 147gr .355" bullets with ease, which offer the same sectional density as 180gr .40 or 230gr .45 and more sectional density is more penetration. In the shorter 9x19 case, these heavy bullets are going to be traveling at under 1100fps, but in the longer .38 Super with its greater powder capacity, ~1200fps is not out of reach. 147gr@1200 is getting into low-end magnum revolver ballistics.
In a single-stack gun like a 1911 or SIG P-220, you could get ten rounds easy; a double stack 1911 or Glock 20-sized gun would be serving up 18-20 rounds of low-end-.357-Magnum-level whupass. I'd be lying if I said the thought didn't intrigue me every now and again.