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(original,ifnull)
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(original,ifnotnull,ifnull)
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’
NULLif(term1,term2)
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!!!
