In the U.S. (in most states, at least), insurance tends to follow the car. If you borrow a friend’s automobile, your friend’s auto insurance is exposed to your hopefully-not-reckless driving.
On-demand car sharing services like Zipcar typically explicitly include insurance. Not surprising, since most Zipcar users typically don’t own cars or have car insurance.
Traditional car rental companies, on the other hand, usually don’t include insurance in their rates. They definitely don’t include comprehensive/collision coverage, and companies in all but a small handful of states either explicitly don’t include any liability coverage (California) or provide very minimal state-minimum third-party liability insurance on a secondary basis as required by law (almost every other state). For more on that, check out this answer: Chris Luth’s answer to What’s the best car rental insurance option for someone without any coverage?
You can purchase non-owner insurance policies (usually liability only) from some insurance providers, but for the most part, these actually wouldn’t do much good for you unless you rent a lot of cars from regular rental companies and don’t want to have to buy their relatively expensive supplemental liability insurance policies (usually $10-15/day for $1 million in coverage). If you have a decent credit card that provides CDW coverage for rental cars, between that and a non-owner policy, you should be OK when renting a car without buying anything from the rental company (though there are a lot more nuances than that and you should probably ask a separate question on this subject if you’re specifically interested in renting cars).
Beyond that, though, when you’re borrowing a friend’s car, for example, your own non-owner policy would be secondary to the car owner’s policy, so it would only provide protection if you were to exhaust your friend’s policy’s coverage limits. It’s still not a horrible idea to protect yourself from exposure to personal liability claims against you in that situation, but in most cases, your own coverage won’t provide you with any real benefit.
One moral of the story here is to be very careful when you borrow a friend’s car (and for the car owners out there, be very careful whom you lend your car to), because the owner is put at risk by whomever is driving his or her car.