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 no gears and is superior to a screw and gear type 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 (deceptively? some may think so) as Micro USM. Unfortunately these still have gears so they don't have the advantages listed above - they are only able to be called "USM" in the barest technical sense.
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.
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.