Alex Rivera | Logout

SQL not equals & null

Asked 2009-03-12T00:14:02.210
10

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.

sql performance oracle-database
Edit
Report

3 Answers

16

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

answered 2009-03-12T00:20:46.703
-1

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'
answered 2009-03-12T00:22:34.517
-3

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.

answered 2009-03-12T00:17:26.633

Your Answer