how to compare null and non null values in single statement in oracle?-CodePudding

I am trying fetch rows when a certain where condition satisfies

select * from users where userid = :userid and objnum = :objnum;

users table has userid(non nullable column) and objnum(nullable column). Now when I want to fetch users with null "objnum" values,it does not work since null values are not comparable.And when I want to fetch objnum value = 1,then above will work.

For null value, I very well get results with below query.

select * from users where userid = :userid and objnum is null;

But my application code is sending the :objnum bind variable as null or integer values and I want to check it directly in single query.Is there a way out to do this in a single query ?

CodePudding user response：

If I understood you correctly, that would be

select * from users 
where userid = :userid 
  and (objnum = :objnum or :objnum is null);

CodePudding user response：

If your application is sending nulls you can do the following as well:

select * 
  from users 
 where userid = :userid 
   and NVL(objnum,-1) = NVL(:objnum,-1);