Determine Oracle null == null

sql oracle null nullable
James Curran picture James Curran · Oct 10, 2008 · Viewed 24.8k times · Source

I wish to search a database table on a nullable column. Sometimes the value I'm search for is itself NULL. Since Null is equal to nothing, even NULL, saying

where MYCOLUMN=SEARCHVALUE

will fail. Right now I have to resort to

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

Is there a simpler way of saying that?

(I'm using Oracle if that matters)

Answer

Andy Lester picture Andy Lester · Oct 10, 2008

You can do the IsNull or NVL stuff, but it's just going to make the engine do more work. You'll be calling functions to do column conversions which then have to have the results compared.

Use what you have

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

Related questions

Update columns with Null values

I tried updating a table as follows: update userloginstats set logouttime = sysdate where logouttime = null; It didn't update the columns with null values. What went wrong?

Read More
Why does Oracle 9i treat an empty string as NULL?

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is …

Read More
Oracle NVL with empty string

I have this table where NULL is the NULL value, not the string NULL: MYCOL -------- NULL example Why does this query not return the NULL row? select * from example_so where nvl(mycol, '') = '';

Read More