Wednesday, 20 January 2016

NULL values in Oracle

One of the wonderful value in Oracle database is NULL value.NULL is nothing. NULL is not even the same as NULL. NULL is unidentified. But we need to work with NULL vales which are no actual values. Oracle provides us with a couple of functions to do the lifting when its comes to checking for NULLs.
expr2 IS NULL

create table empex (empno number,ename varchar2(10));
insert into empex values(1,'abc');
insert into empex values(2,'xyz');
insert into empex values('','nnn');
insert into empex values(4,'');
insert into empex values('','');
insert into empex values('','');
select * from empex where empno is not null;
select * from empex where empno is null;
NULL handling:
Using many pre defined  oracle functions to handled the null values.
NVL function:
select nvl(empno,0),nvl(ename,'III') from empex ;
Here NVL is pre defined function from Oracle.
decode function:
select decode(empno,'',0,empno),decode(ename,'','III',ename) from empex ;
NULL handling using case condition:
select case
when empno is null
then 0
else empno
end empno from empex;

NULL behavior:
Using below query we can find the null behavior.
Scenario 1:
select case when null=null then 1 else 2 end abc from dual;

Here two null values are always not equal. Hence the output displaying as 2.
We can not expect the behavior of NULL.

Second scenario:
select * from empex where empno is null;
here we can see the NULL values using the is null condition.

Scenario 3:
select * from empex where empno=null;

Same condition using the empno=null there is no rows are displaying.

So as for above scenarios we cant expect the exact behavior.