If you Want to get empty vales(null values ) from the db.
What you will do?
Consider the below example:
We have one Employee table.IT is having fields - Emp_id, Emp_name, Emp_salary and Emp_status
Possible values for the Emp_status
are Settled – S
Cancelled
– C
Not
settled – ‘’ or NULL
insert into Employee (Emp_id, Emp_name, Emp_salary,
Emp_status) VALUES ('1','A','10000','S');
insert into Employee
(Emp_id, Emp_name, Emp_salary, Emp_status) VALUES ('2','B','5000','C');
insert into Employee
(Emp_id, Emp_name, Emp_salary, Emp_status) VALUES ('3','C','8000','');
insert into Employee
(Emp_id, Emp_name, Emp_salary, Emp_status) VALUES (4','D','10000',NULL);
insert into Employee (Emp_id, Emp_name, Emp_salary,
Emp_status) VALUES ('5','E','10000', '
');
Select * from Employee;
|
Emp_id
|
Emp_name
|
Emp_salary
|
Emp_status
|
|
1
|
A
|
10000
|
S
|
|
2
|
B
|
5000
|
C
|
|
3
|
C
|
8000
|
|
|
4
|
D
|
10000
|
|
|
5
|
E
|
10000
|
|
Emp_status, will be showing the status of employee Salary. S
and C (Settled/Cancelled), if it is not settled they are not storing any
values(’ ’).
If the user want
to select the not settled records, the records that of NULL values need
to select from DB. So the user will select
The Actual number of not settled records are 3 (last 3 rows
– 3,4 and 5)
Select count(*) from Employee where Emp_status = ''; -- 0
We are
excepting to get count as one for the above query.
But it will not
display any rows, since empty strings behave as null values in Oracle.
Select count(*) from Employee
where Emp_status = NULL – 0
NULL has no datatype, Null is
untyped. So that NULL can fit into any of the datatype column.
But we can't use usual operands
(=,<,> ..) to compare null.
So Oracle is providing IS NULL and
IS NOT NULL condtions to compare NULL values in DB.
Select count(*) from Employee where Emp_status
is null; -- 2
We have 5th
row containing the space. But it will not display for the above query.
Select count(*) from Employee where
trim(Emp_status) is null; -- 3
To display all the
rows which is not having any values we have to use above query.
We have one more
way to compare NULL:-
NVL(expr1, expr2)
If expr1 contains a NULL value, then replace it with the
value of expr2
NVL(trim(Emp_status),'N') - All the Emp_status which is having
NULL will be stored as ‘N’
select count(*)
from Employee where NVL(trim(Emp_status),'N') = 'N'; -- 3
The above query will give correct
count.
