Search code examples
sqloracle-databasenullif

Are arithmetic equations implicitly evaluated in NULLIF()

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?

Solution

  • 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.