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.
-
Please add your query, sample data and expected result.Jens– Jens2015-04-10 06:17:32 +00:00Commented Apr 10, 2015 at 6:17
Add a comment
|
4 Answers
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.
Comments
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
Comments
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
2 Comments
Manish Kumar
IFNULL(E.LocationId,-1)=COALESCE(LocId,E.LocationId,-1) i used this for my solution and its giving me the expected result.Thanks
Iłya Bursov
@ManishKumar yeah, if you need to return all values in case of null locid
Try It:
LocationId=IFNULL(LocId,LocationId)
AND LocationId IS NOT NULL
Or
LocationId=COALESCE(LocId,LocationId)
answered Apr 10, 2015 at 6:16
Stanislovas Kalašnikovas
9,0735 gold badges36 silver badges60 bronze badges