Menu
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)
643
Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function.
Here is an example of how to use the Oracle IS NULL condition in a SELECT statement: SELECT * FROM suppliers WHERE supplier_name IS NULL; This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.
In SQL null is not equal ( = ) to anything—not even to another null . According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null .
Answer: An empty string is treated as a null value in Oracle.
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)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
