Oracle SQL: What is the point of this DECODE statement?

Question

I'm working on maintaining some Oracle sql statements written by someone else, and all over the place I keep seeing this same pattern repeated in lots of statements and pl/sql blocks:

select DECODE(NVL(t1.some_column,'~'),'~',t2.some_column,t1.some_column) some_column from t1, t2 where ...

Now, isn't this identical to this much simpler statement?

select NVL(t1.some_column,t2.some_column) from t1, t2 where...

I'm not sure why the decode and nvl are being chained in the original query. It seems like a less efficient way of just doing nvl alone. Can someone explain this to me?

Thanks for any insight!

Answer 1

Wow, um, looks like pretty poor coding there.

NVL(t1.some_column, t2.some_column)

...gives the same results.

I'm more comfortable with COALESCE, though:

COALESCE(t1.some_column, t2.some_column)

That way, if you ever want to add on a third, it's just:

COALESCE(t1.some_column, t2.some_column, t3.some_column)

It appears that whoever wrote the original code wanted it to be extendible and just didn't know about COALESCE...