Empty String and NULL values in Oracle DB

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 5^th 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.