Replace NVL in SQL query

I have come across a query like this in a database:

SELECT col_a
FROM my_table
WHERE
  nvl(col_b, 0) in
    nvl(nvl(:arg, col_b), 0)

This will return col_a from my table where:

• col_b == :arg
• or if :arg is null return the whole table

This seems like quite a convoluted way to return either or all the records matching the input argument or just sele开发者_如何学编程cting the whole table depending on the value of the input argument.

Is there any simpler way of achieving the same effect?

Also, I note that NVL is Oracle specific, is there any benefit of using it in this instance over COALESCE?

It is rather convoluted. I would prefer:

SELECT col_a
FROM my_table
WHERE (col_b = :arg OR :arg IS NULL) 

There is no benefit of NVL over COALESCE, in fact the reverse. However, NVL has been around longer in Oracle (and is shorter to type) and hence tends to get used more.

EDIT: Actually, my answer doesn't do quite the same as the original SQL, which I suspect was wrong: if :arg = 0 then the original SQL will return rows where col_b is null or 0!