That moth-face shot, field of view maybe under 2mm, would probably have used a 10x microscope objective, on the front of a 200mm lens.
You don't get the same resolution with a stopped-down camera lens, as you do when you use appropriate optics and focus-stack, because diffraction kills it.
But with the wide aperture higher resolution objective you get maybe 10 microns in focus, although, you can combine as many images as you like.
There are no magic lenses, the DOF depends on the aperture and magnification, only. The clever bit is to discover and use the combination which works best for your purpose. It only has to be sharp enough .
You wouldn't be able to make that particular image, or a lot of that photographer's others, with a single shot from an MP-E.
But the moth had to be dead, and not moving, to use focus stacking.
The MP-E's sharpest apertures at higher (4-5x) magnificaitons are wide open, you can stack with that too!