Water is my thing! I'll generalize here between the two. R/O (reverse osmosis, which is essentially pressure separating particles from a solution)-D/I (de-ionization, ion exchange resins bind and filter out metals and mineral salts) filters make the purest water you can from your source (tap or well water). The source water goes through a series of filters, usually a sediment filter first, then ion exchange resins, and mechanical filters that nothing smaller than .5 microns can fit through. The resulting "R/O-D/I" water is usually very soft and low in pH, but this can be corrected (if needed) with mineral salts. The pros are, VERY pure water with 0 TDS, units are reasonably priced at around $200 for a 90 gallon per day unit. Cons are, lots of waste water down your drain, and slow to produce.
Distilled water is essentially steam collected and converted back to water. Some of it is copper distilled, which is deadly to inverts we keep in the aquarium or wish to view under a microscope. Distilled water is usually deemed worthless in aquarium and lab settings involving live specimens. Personally, I'd give my herps tap water before distilled. But I may be overly critical because of the thresholds of aquatic life to copper and other metals.