Fluoride in the water is beneficial in the pre-eruptive phase (when teeth are still growing). Fluoride ingestion increases tooth resistence to cavities if the ingestion happened while they were growing.

This does mean that fluoride in water isn’t really useful after you have all your permanent teeth though.