6

Is there a nicer way of comparing if two values are equal if they can be nulls other than the following?

a = b or (a is null and b is null)
Clinton
  • 22,361
  • 15
  • 67
  • 163
  • 4
    possible duplicate of [Get null == null in SQL](http://stackoverflow.com/questions/191640/get-null-null-in-sql). Note that in general, while there are simpler ways to write this, it is likely the most efficient way, because there are no function references. – DCookie Jun 27 '11 at 04:41
  • I'm curious if that's true, just because `is null` doesn't have parentheses, does not mean functions aren't called; after all, comparisons are still performed. I'd bet that the db has been optimized for `coalesce`, since it is used frequently. I'm not saying you're wrong, just that it'd be interesting to benchmark it with the newer db engines. – vol7ron Jun 27 '11 at 23:06
  • Just tested it on an 11g instance. While not as dramatic, the coalesce uses about 10% more CPU. – DCookie Jul 06 '11 at 03:44
  • ty, DCookie, that's good general information. If anyone could do the same analysis with: How many tuples - fields, records, tables? What's the difference in table size? What was the difference in speed? Just curious. – vol7ron Jul 07 '11 at 18:54

3 Answers3

5

You can:

a=b or coalesce(a,b) is null

You could also use nvl, but that is a legacy function and coalesce is quicker, since it stops at the first non-null

vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • 2
    This answer is misleading: `nvl` and `coalesce` perform equivalently in most uses (passed two columns or a column and a static value). It's only when the second parameter is a function or a complex evaluation (and the first parameter is null) that `coalesce` performs better. – Allan Jun 27 '11 at 13:47
  • Well I think `nvl` only accepts two arguments, whereas `coalesce` will accept a list, so yes, you are correct. The other thing to note is that I believe `nvl` does not require strict datatypes, whereas `coalesce` requires all the datatypes to be the same - there could be some performance implications there. – vol7ron Jun 27 '11 at 16:38
4

You can use DECODE(A,B,1) = 1

DECODE is irregular in its treatment of NULLs.

However I think the intention is unclear and prefer vol7ron's answer. Clarity over minimising typing !

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
0

You can wrap it with nvl and set it to some value not expected in your set:

NVL(a,0) = NVL(b,0)
Haitek
  • 1
  • 5
    Different results if A is 0 and B is null. – Gary Myers Jun 27 '11 at 03:57
  • 1
    I think haitek implied that `0` was the value not expected in the set. I think this is a good example of where it's important to keep SQL comments w/ the code. It's valid, but important for the programmer/maintainer to be aware of the field's possible values; which is probably the very reason I'd shy away from it – vol7ron Jun 27 '11 at 16:44