CODEWITHSUNDEEP
mysqlsql
Genadinik
Genadinik

Reputation: 18649

!= in MySQL not working as I expected

I have a query that worked fine, and then I wanted to add to the the existing where condition something like: and some_column != 1

That column value was null. But the query stopped finding the row it used to find. Any idea why?

Thanks. Alex

Upvotes: 1

Views: 104

Answers (4)

Michael Lorton
Michael Lorton

Reputation: 44406

Almost any expression with null is null. 1 != null is UNKNOWN (and therefore would cause the row to be removed from the resultset). 1 = null is UNKNOWN. 1 + null is UNKNOWN.

The most significant exception: null is null is TRUE.

Upvotes: 2

nzifnab
nzifnab

Reputation: 16110

NULL, by definition, is an "unknown value"... Therefore, null != 1 is an unknown result. The NULL might be 1, and it might not be - but the key is that SQL will not attempt to guess. This is sort of a weird way for SQL to handle the value, but it is what it is.

Therefore, if you want to account for possible nulls you need to change your where to this:

AND some_column != 1 AND some_column IS NOT NULL

Upvotes: 1

Tanvir Kaiser
Tanvir Kaiser

Reputation: 253

using '<>' may work...can give a try

where ISNULL(coloumn_name,1) <> 1

Upvotes: 0

Charles Boyung
Charles Boyung

Reputation: 2481

SQL in just about every form is notorious with its handling of null values in comparisons. If a column is nullable and you want to do a comparison on it, you should use ISNULL:

WHERE ISNULL(column_name, -1) != 0

That should take care of your problem.

Upvotes: 2

Related Questions