Friday, January 23, 2015

Do you know NULL (In Oracle)

We can see all around the web and even in many teams, people who port data from SQL server to Oracle a little frustrating due to the presence of NULL. A thorough understanding of NULL and its implication on various operations is required to avoid this frustration.

In all queries below, I have used default tables in Oracle HR schema.
NULL is not equal to 0. NULL is not equal to NULL .

NULL and arithmetic operators,

When any arithmetic operation (addition, subtraction, multiplication and division)  is performed in the column with null value results in NULL.

To resolve this we can handle the null values using the null functions.
select (null+10) from dual; --- >> results in NULL
select (null-10) from dual;  --- >> results in NULL
select (null*10) from dual;  --- >> results in NULL
select (null/10) from dual;  --- >> results in NULL
Any operation using number or date column using NULL results in NULL.

When using character string and null,
select (null||'accepting null') from dual;   --- >> results in accepting null

IS Null,

select last_name,commission_pct
from employees
where commission_pct is NULL;

is not the same as

select last_name,commission_pct
from employees
where commission_pct = NULL;

The first query returns 50 records whereas the second one returns 0 records against the same table. The first query is the right syntax while the second is not.

NULL when using IN,

select employee_id, commission_pct
from employees
where commission_pct IN ('0.3',NULL);

The employees table has records containing NULL and 0.3 in the commission_pct  column. But the above query only gives the data set with values of 0.3 and ignores the columns with null.

select employee_id, commission_pct
from employees
where commission_pct NOT IN ('0.3',NULL);

The abovne query does not give records do not contain 0.3 or null in commission_pct. But the result set contains no records.

NULL in Single row functions,

select concat('Outer',concat(null,'Inner')) from dual;

Null in the above concat function is not converted to string. The Null is ignored in the concatenation and the result is ‘OuterInner’

Select length(null) from dual;

The result of the above query is null.
Most of single row functions results in null if null is passed to it.

NULL in group functions,

The concept of null and group functions can be discussed about two pointers.

The Null if passed as an argument in group functions Min, Max, Sum, Avg will result in null as these are arithmetic operations.

The Count function returns the count of non-null values of the expression passed to it

Select count(dummy), count(null) from dual results in 0,0

So it returns 0 since the above expression returns null.

Select count(commission_pct) from employees returns 35 even though the column has  107 records, the other 72 rows has null records. To handle this situation we make use of one of the null functions explained below

NULL functions,

The NVL, NVL2 and NULLIF are most commonly used null functions

NVL(employee_address,’NO address for this employee’)  -- This function returns the employee_address column if its not null and if its null returns the string ’NO address for this employee’

NVL2(employee_address,’This employee has address info’,’NO address for this employee’)
This function returns ’This employee has address info’ if this column is not null and if this column has null value returns the string ’NO address for this employee’

The term1 and term2 are compared , if they are equal null is returned else term1 is returned
Nullif(1234,1278) – this function returns 1234 since both are not equal.

One example with both null and group function below,

Select count(nvl(commission_pct,0)) from employees;

This returns all the rows from employees table which is 107 records.

Happy Querying!!!