Which type of stain is best?

No one answer fits all.  There are essentially 3 broad types of stain (from a looks perspective).

1.) Clear - Goes on like water in one coat and allows all of the wood grain and planking to be easily seen.  This is especially nice when you have a brand new deck.  It provides the least weather and UV protection and needs to be done every 2 years, in our climate.

2.) Semi-Transparent - Also allows the wood grain to be seen, but less so than clear.  Water based and oil based are available.  Water based stain provides less protection than oil.  Oil can last up to 4 years, but can provide a more fertile base for algae-type growth.  The darker the color, the greater the UV protection. This is a single coat application.

3.) Solid Color - Essentially a thin paint.  While you can't see the wood grain through this it will provide the best protection from water and sun.  This is the longest lasting stain you can use.  Unlike paint, stain sort of fades away as it ages versus peeling.  Solid color stains typically last from 3 to 5 years. Iy should always be done in two separate coats.

A relatively new entry to the staining category is Behr Deck Over.  The difference is that Deck Over is extremely thick (covering 50 square feet with a gallon versus 300 for standard solid color stain) and can fill small cracks on boards while staining.  This product has not been out very long it is not possible to comment on the expected longevity.  Prep is even more important when using this type of stain since any failure will lead to thick layers of peeling.  Once this product is used you cannot go back to standard stain without fully stripping the deck.