SQL not equals & null

Question

We'd like to write this query:

select * from table 
where col1 != 'blah' and col2 = 'something'

We want the query to include rows where col1 is null (and col2 = 'something'). Currently the query won't do this for the rows where col1 is null. Is the below query the best and fastest way?

select * from table 
where (col1 != 'blah' or col1 is null) and col2 = 'something'

Alternatively, we could if needed update all the col1 null values to empty strings. Would this be a better approach? Then our first query would work.

---

Update: Re: using NVL: I've read on another post that this is not considered a great option from a performance perspective.

Answer 1

I think that your increase would be minimal in changing NULL values to "" strings. However if 'blah' is not null, then it should include NULL values.

EDIT: I guess I'm surprised why I got voted down here. If 'blah' if not null or an empty string, then it should never matter as you are already checking if COL1 is not equal to 'blah' which is NOT a NULL or an empty string.

Answer 2

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

Answer 3

For Oracle

select * from table where nvl(col1, 'value') != 'blah' and col2 = 'something'

For SqlServer

select * from table where IsNull(col1, '') <> 'blah' and col2 = 'something'

Answer 4

In oracle use the nvl function

select * from table where nvl(col1,'notblah') <> 'blah'

Answer 5

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.

Answer 6

It depends on your data, but most optimizers are going to look at col2 before col1, since = is an easier index than !=.

Otherwise, there are various ways you can speed this query up. It's probably best to do (col1 != 'blah' or col1 is null), but some database allow you to index a function. So you can index coalesce(col1, 0) and get good performance.

Really it depends on you data and your table.

Answer 7

If you want to speed up this sort of query, and you're on Oracle 10g or later, use a function-based index to turn those NULLs into values:

CREATE INDEX query_specific_index ON table (col2, NVL(col1,'***NULL***'));

select * from table 
where NVL(col1,'***NULL***') != 'blah' and col2 = 'something';

The database will quite likely use the index in this scenario (of course, subject to the decision of the CBO, affected by row counts and the accuracy of the statistics). The query MUST use the exact expression given in the index - in this case, "NVL(col1,'***NULL***')"

Of course, pick a value for '***NULL***' that will not conflict with any data in col1!