I think "it all depends"! I see alot of macro shooters using microscope lenses mounted on all sorts of adaptors. Others are using extension tubes. And there are also automated camera controlers that can take 300 microstepped photos and focus-stack them for added depth-of-field.
Lots of info out there using google... One of my favorites is "no cropping zone"
Canon 5DmkII, EF 24-105mm f/4L IS USM,
To me the main two variables for macro lenses are the minimum working distance and the magnification ratio. The typical 60mm, 100mm, 180mm etc. lens with 1:1 will all be capable of great results - the difference will be how close you have to get, and how busy the background will be.
A quick example of the background difference is part way down in this review of the Canon 60mm: 60mm Macro
Extension tubes are a good and cheap way to increase magnification without having to have a specific lens like the one Martine links to.
Note that the picture you show is taken by a Canon PowerShot A650 IS :) That helps with depth of field, but not working distance !
I have been wondering the same thing. I have the Sigma 105mm Macro lens, but I cannot seem to get the subject on photo like such pictures. Is extension tubes or convertors good if you want to get a better magnification of the subject? And how does it work? I know with the convertors you will lose a few stops, so you have to work around that but how about the extension tubes?
Just using extension tubes is handy for getting magnification but unless you use ET lengths smaller than the focal length of the lens, your working distance gets very low. Also focus stacking is imperative if you were to use the lens at wide apertures.
I've read of people using a combination of extension tubes and teleconverters as well. Depending on the order in which they are fixed, your magnification and working distance can be controlled. Meaning: Camera->ET->Converter->Lens would give a different result to a Camera->Converter->ET->Lens combo.
I've also tried (with poor results) mounting a lens in reverse over another. It is a frustrating process and comes with many problems, vignetting being one. Still, if you have two lenses, you can give it a try.