Congratulations! You're in a very small minority!
Only if you cherry pick what ammo you compare against each other.
Again, it depends on where you shop, and what you are buying, how much of a price difference you see, and how important that amount is, to you.
As to moon clips, you cannot use them at all in SA revolvers. You cannot use them in DA revolvers, UNLESS the gun is made or remade for that. Not all are/were. And the rimmed version of the 9mm Luger got pulled off the market pretty quick when someone found out it would fit in .38 S&W guns and had about double the allowable chamber pressure!
SO, while you may believe the 9mm Luger revolver makes more sense than the .38 Special, the rest of the world, including gun makers and the buying public, apparently, don't.
I may be in the minority, but I have my 9mm 642-1 on me almost all the time when I’m not working. Hell, have it on me right now.
For ammo, I was fortunate to be able to compare the same revolver in both calibers, as I have the original cylinder and the converted 9mm cylinder. Hornady CD 110 grain .38 +P was going the same speed out of it as Hornady XTP 147 grain 9mm (standard pressure). If I’m a betting man, I prefer heavier weight going the same speed. If you are carrying a gun… you are a betting man. If you have to use it, do you want the odds stacked on your side or against you?
For moon clips, not understanding the argument. If you have a 9mm revolver… most are cut from the factory for them. If you are converting… it usually is included in the service. Converting from .38 or .357… you’ll need moon clips for headspace. Can still shoot the original caliber, but will bulge the case/possibly stick. Not that big for me, but I know some people want that option.
Personally, I like 9mm in moon clips out of a .38/.357 length cylinder/ejector rod. Longer cases, more of a chance of them getting hung up on the grips/frame. Anyone with a J-frame likely dealt with it at least once… and is what it is. Even loaded 9mm in moon clips clear the cylinder on extraction. Empty casings… clean extraction.
But what it comes down to it, there are drawbacks in every decision made with firearms. Revolver verses autoloader. 9mm verses .40. .45 verses 10mm. 124 grain verses 147 grain. Colt verses S&W. The list goes on…
What are 9mm revolver drawbacks? Not as common, as they had bad press from way back when. The 940s were plagued with issues of sticky casings. The one 942 made got a bad review from Wiley Clapp… at a time where if a gun writer said something bad, people took it to heart. And then the few that like them, or swear by them… there are the countless arguments against them by people who never handled one (going to blow up the gun, recoil is too bad, etc).
It’s a automatic cartridge, and not being in a magazine can have issues (crimp jump). Solved pretty easily… buy ammo that has a good crimp. Hornady is good to go across the line. Shy of that, I know UMC bulk used to work well (at least held for four shots… fifth is that one going off), and it was identical POI as my carry load.
In the end, carry what you want. As long as it goes boom, you are confident with it, and it does what you need it to… zero reason for argument. Why someone chooses X is always a good discussion, and it gives a viewpoint that others can look at and decide if it works for them. My 9mm 642-1 isn’t for popularity or to make me look cool… it’s on my belt because it checks all the right boxes for me and that I trust it 100%.