Oracle 12 equal and not equal (=, !=, <>) behavior about nvarchar2 working problem

Question

In oracle an empty varchar2 and null are treated the same because Oracle internally changes empty string to NULL values. Please consider following example :

--1- create table
CREATE TABLE persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50)  ,
    last_name VARCHAR2(50)  ,
    PRIMARY KEY(person_id)
); 

--2-insert sample data
insert into persons (first_name,last_name) values('n1','l1');
insert into persons (first_name,last_name) values('n2',null);
insert into persons (first_name,last_name) values('n3','');
insert into persons (first_name) values('n4');

the following query has result :

select * from persons where last_name is null;

PERSON_ID FIRST_NAME      LAST_NAME                                        
    2      n2                                                                                                   
    3      n3                                                                                                   
    4      n4

and

 select * from persons where last_name is not null;
    PERSON_ID FIRST_NAME       LAST_NAME                          
        1      n1                  l1

My question :

1-Why this queries result are “no rows selected”

select * from persons where last_name =''; --   no rows selected
select * from persons where last_name !=''; --  no rows selected

2-More important question is wonderful behavior for the following query:

select * from persons where last_name !='' or last_name =''; --  no rows selected

score 2 · Accepted Answer · answered May 13 '20 at 12:18

1-Why this queries result are “no rows selected”

Because the answer lies in your question itself.

select * from persons where last_name =''; -- here '' is converted to NULL
-- so last_name = NULL 
-- it will always fail as NULL can not match with other NULL also

select * from persons where last_name !=''; --  - here '' is converted to NULL
-- so last_name != NULL 
-- it will always fail as NULL can not mismatch with other NULL also

2-More important question is wonderful behavior for the following query:

select * from persons where last_name !='' or last_name =''; 
--  equal or not equal with NULL will always return false. 
-- so the result is no rows for this query also

Please note that in any expression if one side is NULL, it will always return false. (IS NULL or IS NOT NULL need to be used for checking null values)

Oracle 12 equal and not equal (=, !=, <>) behavior about nvarchar2 working problem

1 Answers1