Reverse Osmosis is a principle that uses the rule that water flows from a high concentration into a low concentration of dissolved minerals, salts etc and can be used to generate 'pure' water. It is not as pure as distilled water but is still too raw for use with fish and must be prepared using mineral additives to make it habitable.
The great advantage of going through this process is that you now have a blank canvas and you can set the water parameters as you want, much easier than with normal tap water.
The good units have a pre-filter to protect the delicate membrane from damage.
The membrane is where most of the action takes place, water is forced through under pressure and the minerals, toxins etc are left behind. This generates a LOT of waste water compared to useful water so if you are metered it may not be economical.
Most units need to be plumbed in but may have self piercing fittings.
I would say it would be a great benefit to all fish but is only used to its full potential with Discus, Altum angels etc or marines.
De-ionised water is similar but the toxins are absorbed or adsorbed by special resins.
Ken
