Sometimes the AF motor in a lens is a screw and gear type, and in that case it's really not much different to an in-body motor except that it actually has the disadvantage of being more bulky. This is the case on many lower cost lenses (including many kit lenses).

At other times the AF motor in a lens is of the ultrasonic motor type, which uses direct drive, requiring no gears and is superior to a screw and gear type used in camera bodies for several reasons:

• It is very quiet (often called "Silent Wave Motor")
• It can move fairly quickly
• It can be less bulky
• It can allow manual focus override without disengaging autofocus

The Wikipedia page on the ultrasonic motor (which was apparently pioneered by Canon) is quite interesting and has diagrams.

As mattdm has helpfully pointed out in the comments, some newer screw and gear type autofocus drives are being marketed by Canon as "Micro USM". This is, in my opinion, deceptive. These still have gears so they don't have the advantages listed above.

A lens motor in your camera body only adds a small amount of bulk, and means that your lens selection is a little better and you can AF with some of the cheaper lenses. If you think you'll be served fine by lenses with in-built motors, it doesn't matter so much whether you have it in the body. Obviously, the ultrasonic motor or SWM lenses are the better technology and these don't depend on a lens motor in the body.

As has been answered before, if both your camera body and lens have an AF motor, the lens one is used. And that's the way you'd normally want it because these are usually superior.

This is not an issue with Canon, who do not produce in-body autofocus motors.