CODEWITHSUNDEEP
mysqlnull
MrrMan
MrrMan

Reputation: 158

Comparing NULL with INT

I am trying to pull all rows where two fields don't match. I understand that nulls are essentially "unknowns" and so have taken steps to define nulls as 0's. As below.

Select ifnull(field1, 0), ifnull(field2, 0) 
from table
where field1 != field2

Can anyone tell me why this doesn't work? I still get no results even though i feel i am defining nulls as "0" with ifnull(field1, 0) correctly.

Upvotes: 1

Views: 149

Answers (3)

MadsBinger
MadsBinger

Reputation: 53

You should move the IFNULL() down to the WHERE clause like this: 

Select ifnull(field1,0) as 'Field1', ifnull(field2, 0) as 'Field2'
from table
where ifnull(field1,0) != ifnull(field2,0)

Upvotes: 0

mrateb
mrateb

Reputation: 2499

Because 0 is inserted after and not before you select

Should be instead:

Select * from 
((Select *,ifnull(field1, 0) new_field1, ifnull(field2, 0) new_field2
from table) As query)
WHERE new_field1 != new_field2;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

It doesn't work because that is how NULL values work in relational databases. Almost all comparisons return NULL, which is treated as false.

Also, what you do in the SELECT has no impact on the WHERE. The fields are still coming from the original table.

More importantly is what you can do. The simplest method is to use a NULL-safe comparison:

where not field1 <=> field2

Upvotes: 2

Related Questions