Are arithmetic equations implicitly evaluated in NULLIF()

Question

My SQL Fundamentals 1 Exam Guide states,

select NULLIF(1234, 123+1) from dual;
The arithmetic equation is not implicitly evaluated...

However, when I submit the query below the result is null - it seems that 123+1 is evaluated (although I know that the queries are different).

select NULLIF(124, 123+1) from dual;

So, which is correct (for the 1Z0-051 Exam, anyway)? Are expressions evaluated or not?

Your query does **not** return `null` for me, but `1234` (the first expression as stated at http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions102.htm when the expressions are not equal) - which version of oracle are you using? See http://sqlfiddle.com/#!4/e61514/3 — Andreas Fester, Jan 10 '14 at 12:07
I tried it in SQLFiddle for 11gR2, and it returns null for me — Incognito, Jan 10 '14 at 12:10
@Andreas I think you have used the query which OP has posted, which has an error. He has used 123+1, instead of 1233+1 :) Check this SQLFiddle http://www.sqlfiddle.com/#!4/d41d8/23313 — Incognito, Jan 10 '14 at 12:11
Please [don't alter code in questions](http://meta.stackexchange.com/questions/101583/when-is-it-appropriate-to-edit-someone-elses-code?rq=1) @Yaroslav. Ask the poster if it's incorrect and request that they change it. It's often the reason for the problem in the first place. However, in this case the OP seems well aware that 123+1 is not equal to 1234, _"although I know that the queries are different"_. In changing the code you also invalidated Nicholas' answer, which makes the entire Q&A a little pointless. — Ben, Jan 10 '14 at 13:33
@Ben My apologies. Have thought of that when it was too late. Thank you for correcting it back. — Yaroslav Shabalin, Jan 10 '14 at 14:07

score 7 · Accepted Answer · edited Jan 10 '14 at 12:26

Nullif() function evaluates both arguments and returns NULL if argument1 equal to argument2. In your case 123+1 is not equal to 1234. 123+1 is equal to 124 not 1234.

the result is null

It cannot be null in your case.

SQL> select nullif(1234, 123+1) as res
   2    from dual;

       RES
----------
      1234



SQL> select nullif(1234, 1233+1) as res
  2    from dual
  3  ;

       RES
----------
 NULL

FOLLOW UP: My 2 cents

The above NULLIF is equivalent to the below case

CASE WHEN 1234=123+1 THEN NULL ELSE 1234 END

You cannot specify the literal NULL for the first expression.

Also from documentation

If both arguments are numeric datatypes, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. If the arguments are not numeric, then they must be of the same datatype, or Oracle returns an error.

Another SO downvote mistery... Got a bunch of those myself. Seems to get more common these days. — rsenna, Jan 10 '14 at 12:20
@Nicholas Krasnov: So, the answer is that the expression `123+1`**is** evaluated, correct? Thanks again. — Jeff Levine, Jan 10 '14 at 23:30

Are arithmetic equations implicitly evaluated in NULLIF()

1 Answers1