get null values with decode

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?

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')