NULL is an
Unknown value. If there is nothing in any column of the table, then it is known
as NULL value. NULL and Zero values are not same. You cannot use NULL instead of zero because
both are not equivalent.
If any
arithmetic expression contains NULL, then its result will be always NULL.
Null operations in SQL
Using NULL with SQL functions.
NVL
You can use
NVL operator to return a value if the NULL value occurs.
EX:- NVL(Emp_Commission,
0)
It will
return Zero value if the Emp_Commission is NULL or it will return the
Emp_Commission if it is not null.
Compare NULL Value
IS NULL
You cannot check the NULL by using the comparison
operators <, =, > and <>.
If you want to test NULL value, you can use IS NULL
operator in the SQL query as shown below.
SELECT EMP_NO, EMP_NAME, SALARY
FROM EMP
WHERE BONUS IS NULL;
This query will return all records from EMP table whose
BONUS column contains NULL value.
IS NOT NULL
You can also use IS NOT NULL operator for comparing NULL
value.
The below query returns all employees whose Bonus is not
null
SELECT EMP_NO, EMP_NAME, SALARY
FROM EMP
WHERE BONUS IS NOT NULL;
