I have a table with names, where the name can be null. If I want to get all the rows with null names, I do the following:
select * from table1 where name is null;
but I want to make sure that if I specify a value it will get the non-null values and if it won’t get the null values
select * from table1 where name in decode (: var, null, name)
So I get the values that are not null when the var is null, how can I get the values that are null?
Advertisement
Answer
You seem to be looking for a null-safe equality. In Oracle, that would look like:
where name = :var or (name is null and :var is null)
The condition succeeds if either of the following conditions is true:
- the values are equal (this implies that none of them is
null) - both values are
null
If you know in advance a value that is not available in the column, you can use coalesce() or nvl():
where nvl(name, 'N/A') = nvl(:var, 'N/A')