CODEWITHSUNDEEP
mysqlsql
Manish Kumar
Manish Kumar

Reputation: 595

Comparing NULL value in MYSql

i have a condition in where clause like:

LocationId=IFNULL(LocId,LocationId)

here LocId is NULL so by IFNUll its returning LocationId which is Also Null in this case this query is not returning anything. Please tell me the solution for it.

Upvotes: 3

Views: 85

Answers (4)

Iłya Bursov
Iłya Bursov

Reputation: 24146

mysql NULL is not equal to NULL, check here https://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

so, your logic should be like this:

select ...
from ...
where
(
    (LocId is null and LocationId is null)
    OR
    (LocId is not null and LocationId = LocId)
)

you can write it shorter, usually Ids are positive values, so you could compare:

select ...
from ...
where
ifnull(LocId, -1) = ifnull(LocationId, -1)

UPDATE: in mysql also exists non ansi standard null-safe comparison, which is shorter, check @Ja͢ck answer

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173542

You could use the null safe comparison operator:

LocationId <=> IFNULL(LocId, LocationId)

The difference between the regular = operator is that the null value is not treated as special, e.g. if both left and right hand argument are null it will yield 1.

See also this answer for more examples.

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

You should understand that you cannot use any relational operator with NULL.

The only option is to use IS NULL or IS NOT NULL or the <=>

So you need to try like this:

LocId IS NULL and LocationId IS NULL
OR
LocId IS NOT NULL and LocationId = LocId

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

Try It:

LocationId=IFNULL(LocId,LocationId)
AND LocationId IS NOT NULL

Or

LocationId=COALESCE(LocId,LocationId)

Upvotes: 0

Related Questions