Hi Carol. Great question.

In addition to what psinger1 mentioned about SQL standards vs. Oracle proprietary, I'd like to add that the two functions have different purposes. DECODE checks for multiple possible values, "decoding" them by returning only one replacement value for each match. COALESCE checks for only a single value -- NULL -- and returns one of several possible values for it.

You could certainly use DECODE this way, but it would be a bit of a chore. You'd have to nest the DECODEs, one for each expression you're testing, and check for NULL in each one.

COALESCE is designed to test for NULL and only NULL, so no nesting is required. A single flat COALESCE will evaluate the expressions looking for the first non-null one.