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

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 2

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

Answer 3

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 4

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 5

What about this option. I think it may work if your value is never null.

where not (value = column)

which would result in following truth table for evaluation for the where clause

                    col1
              | 'bla' |  null |
              -----------------
      | 'bla' |   F   |   T   |
value -------------------------
      |  null |   T   |  *T   | 

*this is the only one that's "wrong" but that's ok since our value is never null

Update

Ok, I just tried out my idea and it failed. I'll leave the answer here to save time of others trying the same thing. Here are my results:

select 'x', 'x' from dual where not ('x' = 'x');
0 rows
select 'x', 'y' from dual where not ('x' = 'y');
1 row
select 'x', 'null' from dual where not ('x' = null);
0 rows
select 'null', 'null' from dual where not (null = null);
0 rows

Update 2

This solution works if your value is never null (matches the truth table above)

where ('blah' != col1 or col1 is null)

tests here:

select 'x', 'x' from dual where ('x' != 'x' or 'x' is null);
0 rows
select 'x', 'y' from dual where ('x' != 'y' or 'y' is null);
1 row
select 'x', 'null' from dual where ('x' != null or null is null);
1 row
select 'null', 'null' from dual where (null != null or null is null);
1 row

Answer 6

In oracle use the nvl function

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

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!

Answer 8

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 9

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.