What can each do that the other cannot? Is it wise to stick to a particular one or are there use cases for each?
Great Prophets are much more powerful (as their name would suggest). They can found and enhance religions, construct holy sites, and spread their religion 4 times. And when converting units, they can also remove existing religions from the city as well.
Missionaries can only spread religion, and they can only do so 2 times (3 if you have the Great Mosque of Djenne). Also, if you end your turn with a missionary in a rival's territory where you do not have an open borders treaty, they lose 25% of their maximum conversion power. If they lose all their power, they disappear (called attrition).
Both of these units can enter into another player's territory without starting a war, even if you don't have an open borders treaty with that country.