SQL not equals & null SQL not equals & null sql sql

SQL not equals & null

In Oracle, there is no difference between an empty string and NULL.

That is blatant disregard for the SQL standard, but there you go ...

In addition to that, you cannot compare against NULL (or not NULL) with the "normal" operators: "col1 = null" will not work, "col1 = '' " will not work, "col1 != null" will not work, you have to use "is null".

So, no, you cannot make this work any other way then "col 1 is null" or some variation on that (such as using nvl).

I think that the solution that you posted is one of best options.

Regarding to performance, in my opinion it is not a big difference in this case, if the clause already have a != comparison usually the optimizer won't use an index in that column, because the selectivity is not enough, so the more discriminating filter will be the other side of the "and" condition.

If you ask me, I won't use an empty string as a null, but may be is just a personal preference.

While not the most readable - Oracle has an LNNVL Function that is essentially the not() function, but inverts the behavior for nulls. Meaning that comparing anything with null inside of lnnvl will return true (I don't know what performance implications this may have).

To do what you want in a single statement:

select * from table where lnnvl(col1 = 'blah') and col2 = 'something'

Note that this will only work for comparing a nullable value against a value you can be assured is non-nullable. Otherwise you'll need to do as Thilo suggests - use an operator similar to

lnnvl(nvl(col1, -1) = nvl(col2, -1))