I turn the water off when leaving because three times I've returned to a flooded home. Once from a failed plastic water supply line to a refrigerator, once due to a slab leak and once due to a failed water heater.
I'm not comfortable leaving the home with the water main on when gone more than overnight. I also would not feel comfortable turning the water main off and leaving the water heater on. A failed tank could cause the heating elements to melt down.
It's all about risk.