Totally application driven.
A full suspension frame could be made out of concrete and not feel much different than Al or steel or whatever. You've got pretty huge shock absortion so the subtleties of frame material are almost completely masked. That's why you see most full suspension frames made out of aluminum, it's cheap, strong and light. Carbon won't make the bike feel much different but will make it lighter.
As for hardtails, unless you're concerned about weight I would go with Ti or Steel over Aluminum and carbon. Aluminum won't give the small bump absorption of the other two materials and carbon is pricey and more vulnerable to crash damage.