How do the following screw types fare in an outdoor (wet) long-term application?
Stainless steel screw:
The stainless steel screw will absolutely be the best screw to resist rust. Stainless steel screws are rust-resistant throughout the entire screw, not just on the surface.
The other screws are only covered with a rust-resistant coating on their surface, which will break down or wear off over time. Galvanization is a process that coats with zinc. Other screws may be zinc coated as well using another process, or they may be coated with something else.
I have some screws on an outdoor fence which have rusted inside the slots in the screw head, because the screw driver tip wore away some of the coating in the slots at time of installation. Phillips-head screws in particular are notorious for getting ground up when the tip "cams out" and jumps out of the screw head.
Stainless steel is undoubtedly the best material for resisting rust and corrosion in screws. Why, then, would anyone use anything other than stainless steel outdoors? Two reasons:
(1) Stainless steel is slightly softer than the hard steel used in deck screws or other similar screws. You can't just "go crazy" with the power tools and slam the screws into place. It is easier to chew up the heads on stainless screws, or to snap the heads right off by over-torquing them (though that is more of a problem with bolts than wood screws). You have to slow down and be a little more careful.
(2) Stainless steel screws are more expensive. Galvanized or other coated screws are just plain cheaper.
Despite those drawbacks, the rust prevention is unmatched by other screws. I live in a rainy wet climate, so I use stainless screws on anything outside that I might need to ever take apart again.
Stainless requires exposure to oxygen to avoid rusting; it's the chromium oxide layer that protects the steel. When completely buried, the chromium oxide layer breaks down and the residual oxygen starts a process called crevice corrosion. Passivization restores a fresh layer of chromium, but passivization cannot be done once fasteners are buried. This is why bronze fasteners are used on wooden boats; the bronze does not need a supply of fresh oxygen. Unfortunately, bronze fasteners are very expensive.
Steel , galvanized or coated, start failing in a deck at 10 years and all fail by 20 years. SS will last "forever". Because SS can only be cold worked. the shanks are soft ( The heads and threads are cold formed so stronger). So they need a clearance hole in the top board and a pilot hole in the bottom board. SS are not used in sea water because the chloride is very corrosive. Copper alloys are used because they inhibit marine organisms and are corrosion resistant. One nuisance of SS fasteners is there is no standardization of the drive so you could need 4 different drivers.
Stainless fasteners are subject to crevice corrosion where not exposed, and are not acceptable for underwater fastening by the U.S. Coastguard on inspected vessels. 316 stainless steel has the best corrosion resistance but stainless steel fasteners commonly sold are 18 series. I've seen stainless screws on boats almost completely corroded away at the joint between two boards.
Bottom fasteners accepted by the Coast Guard are hot dipped galvanized steel and bronze. Stainless steel and brass are not accepted.