What is the answer of 100 added to null in SQL?

Question: I want to know the answer of this question: 100+null=? in sql


Answer: This is a question that goes to the heart of SQL. What is NULL? Null in Oracle means a value is undefined or unknown, so the short answer to the question is "unknown" - in other words NULL. In fact the result of any arithmetic operation involving a NULL value is always NULL. This can cause problems because the expression x=NULL is always false!

Fortunately Oracle provides us with several tools for dealing with unknown values. There is the reserved word NULL in both SQL and PL/SQL and a number of functions, the most commonly used of which are NVL and DECODE.

The DECODE function enables us to change the value of a column retrieved by a query and is often used to convert numeric codes into something more meaningful. For example, the following SQL statement converts a NULL (unknown) numeric value into the character string 'unknown'.

SELECT DECODE(NULL+10,NULL,'unknown') FROM dual;

The NVL function does the same job but will only convert NULL values whereas the DECODE function can be used for any value. The NVL function also only allows conversion to a value of the same type. This means the following SQL statement generates an "ORA-01722: invalid number" exception in Oracle.


SELECT NVL(NULL+10,'unknown') FROM dual;


This occurs because Oracle casts the expression NULL+10 as a number and so the NVL function expects a number as the value to assign to the result of the expression instead of NULL as in the following example which produces a result of 0.

SELECT NVL(NULL+10,0) FROM dual;

By now you might be thinking what happens with NULL character strings. The answer is these are regarded as empty strings. This means that we can use NULL strings in certain expressions. For example, the following SQL statement returns 'XX' instead of 'unknown' because we concatenate our empty string with the character string 'XX' leaving 'XX' as the final result.

SELECT NVL(NULL||'XX','unknown') FROM dual;

The other SQL functions handling NULLs are
  • COALESCE
  • LNNVL
  • NANVL
  • NULLIF
  • NVL
  • NVL2
For details on these functions see the Oracle SQL Language Reference Manual.

For more help on NULLs see this question and answer.

If you need more help with Oracle, why not take a course with one of our partners. Smartsoft Computing offer instructor-led Oracle training courses in the UK, in the New York/Boston areas in the U.S, as well as classroom-quality, on-line training.

Comments for What is the answer of 100 added to null in SQL?

Average Rating starstarstarstarstar

Click here to add your own comments

May 14, 2012
Rating
starstarstarstarstar
good
by: Anonymous

nice explanation

Jul 09, 2011
Rating
starstarstarstarstar
Thnx
by: Vishal D. Kolekar

Hey.............
Very Good explaination
Thanks...

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.