0

Just looking at a query and notice it uses NVL(table.column, '') with nothing between the quotes.

What function would this serve? Isn't that equivalent to a NULL?

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
farrellmr
  • 1,815
  • 2
  • 15
  • 26
  • 2
    @sgeddes: I see no problem whatsoever with his question, on the contrary, absolutely legitimate (see the answers). Worth noting that his question is way more useful than your remark. It's possible that you misread the question and thought he asked what does NVL do. – Veverke Feb 26 '15 at 14:16
  • 1
    @LittleBobbyTables: please at least point out where in the docs they give such an example. Keep in mind that he did not ask "what does NVL do?" – Veverke Feb 26 '15 at 14:18
  • @Veverke - the documentation clearly explains how `NVL` works, and if you know that `''` in Oracle is NULL (or Google "Oracle blank string" and see all the documentation/links from there), then - ta da - you already have your answer. Also, this should be extremely easy to test in an environment. – LittleBobbyTables - Au Revoir Feb 26 '15 at 14:21
  • @LittleBobbyTables - But still this is a valid question not too many people know about this. – Pரதீப் Feb 26 '15 at 14:21
  • That's not the point - the question in the first place arouse because he FOUND such as thing in his application code. A cautious developer double checks whether a statement he judges is useless should indeed be removed. – Veverke Feb 26 '15 at 14:23
  • Thanks for the support - i think it was a valid question. The answers about how this can prevent "unwanted index usage" may justify its use – farrellmr Feb 26 '15 at 14:34

5 Answers5

3

Yes it would be bad programming practice to write that in oracle. It essentially keeps the same value as-is no matter what the column value is. It might even hurt you in performance by not using any indexes that you might have on those columns if this is in the where clause.

ruudvan
  • 1,361
  • 6
  • 16
1

In Oracle it would do nothing, as empty string IS NULL. In other DB though, it wloud return NOT NULL value of empty string if table.column wloud be NULL.

Here is a link to more thorough explanation of this behaviour.

Community
  • 1
  • 1
T.Z.
  • 2,092
  • 1
  • 24
  • 39
1

In Oracle, yes, that's a pointless expression.

In most databases, however, it would convert NULL values to the empty string which is different from NULL (here's a separate question on why Oracle treats the empty string as NULL). I would guess that whoever wrote the query was expecting Oracle to follow the ANSI standard and that the code in question has a bug or at least is not functioning the way the author intended.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

From oracle documentation:

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Rubik
  • 1,431
  • 1
  • 18
  • 24
0

This call is equivalent to simple field reference (table.column).

Such expression could have sense if used in the where part of a query (it can be part of optimization preventing unwanted index usage).

Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28