Oracle Sql Select statement do not evaluate null values. Null values should be handled in the where condition of sql statements for all nullable columns. Oracle nvl function provides best way to handle null values.
create table emp(
emp_id number,
emp_name varchar2(20),
emp_age number);
alter table emp add constraint emp_pk primary key (emp_id);
insert into emp values(1,'mahesh',null);
insert into emp values(2,null,20);
insert into emp values(3,null,23);
insert into emp values(4,null,null);
An employee table contain column named emp_name that can have null values. When emp_name column is compared with "like" operator the rows with null values for emp_name columns are not evaluated by the select statement.
select * from emp where emp_name like '%';
EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh
To evaluate column that can have null values, nvl function can be handled for emp_name column for null values as shown below.
select * from emp
where nvl(emp_name,'!') like '%';
EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh
2 20
3 23
4
