In Oracle NULL means absence of data. A NULL can be assigned but it cannot be equated with anything. NULL cannot be equated with NULL itself. NULL is one of the confusing feature in Oracle.
Examples :-
Consider below table SALARY with two columns NO and SAL. In 3rd and 4th rows SAL is NULL.
NO | SAL |
1 | 2000 |
2 | 3000 |
3 | NULL |
4 | NULL |
5 | 1000 |
1) List all records with SAL is NULL
select * from salary where sal is null;
NO SAL
———- ———-
3 NULL
4 NULL
Note :- In SQL it will represent as is null;
2) List all records with SAL is not NULL
select * from salary where sal is not null;
NO SAL
———- ———-
1 2000
2 3000
5 1000
3) Insert statement with NULL
insert into salary(no,sal) values (6,null);
4) Deleting all records with SAL as NULL
delete from salary where sal is null;
5) Update statement with NULL
update salary sal = null where no = 5;
6) NULL cannot be equated with NULL itself
select * from salary where null = null;
no rows selected
NULL just means a data that “i don’t know”. That’s it! Isn’t it simple?
Yes,Null means just absence of data