Change Null with 0

Question

I have tried to run a query I phpmyAdmin as follows

SELECT
  orders_history.id,
  orders_history.`items`,
  orders_history.`transaction_id`,
  orders_history.`quantity`,
  estockClaims.`esquantity`,
  IFNULL( esquantity, 0 ),
  orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims
  ON orders_history.transaction_id = estockClaims.transaction_id
 AND orders_history.items = estockClaims.items
LIMIT 0 , 100

And it gives me this result:

       ----------------------------------------------------------------------
       id     items      transaction_id    quantity   esquantity   IFNULL(esquantity , 0 )   myquantity
       1      FR               001           100        NULL              0                        NULL
       2      BR               002            10        NULL              0                        NULL
       3      WR               003            25        25               25                        0
       4      CR               004            50        3                 3                        47

How to solve this so that NULL is not NULL anyomre but change to 0. Thanks in advance.

Thanks

http://stackoverflow.com/questions/3532776/mysql-replace-null-with-0 — nobalG, Nov 17 '14 at 12:35

Shomz · Accepted Answer · 2014-11-17T12:51:36.883

You already have it in the next column. What you need to do is to drop the original esquantity column and make an alias for the IFNULL... column, like this:

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` , 
       orders_history.`quantity` , IFNULL( esquantity, 0 ) AS esquantity, 
       orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

The change I mentioned is in the line 2 above.

UPDATE

To get

orders_history.`quantity` - estockClaims.`esquantity` AS myquantity

to show expected results, you need to "unnullify" the esquantity field again so that the subtraction would work:

orders_history.`quantity` - IFNULL( estockClaims.`esquantity`, 0 ) AS myquantity

That would ensure you no longer get, for example:

100 - NULL

but this instead:

100 - 0

which will return a proper value.

You can also skip the whole subtraction thing if esquantity is NULL and simply use the value of quantity.

It works for the esquantity column. But why the result from the ` `orders_history.`quantity` - estockClaims.`esquantity` AS myquantity` ` is still NULL. What I want is it still the same with the first quantity. — Al Kasih, Nov 17 '14 at 12:39
Because you also need to use 0 there instead of NULL, I'll write you an update. — Shomz, Nov 17 '14 at 12:40

score 0 · Answer 2 · answered Nov 17 '14 at 12:38

0

you can use IF to check the esquantity and myquantity columns:

IF(esquantity IS NULL, 0, esquantity)

and

IF(myquantityIS NULL, 0, myquantity)

or use IFNULL as DanFromGermany said

answered Nov 17 '14 at 12:38

No Idea For Name

11,411
10
42
70

1

`IF (... IS NULL, ...)` is the same as `IFNULL( ..., ...);` + answer not helpful, where to use it? – Daniel W. Nov 17 '14 at 12:39
1

I think you should actually ask the upvoter to explain why this answer got an upvote - @DanFromGermany explained it to you why it's completely not helpful (or should I say completely useless) because OP is already doing IFNULL... You should focus more on giving good answers rather chasing points. Btw, I'd say you should be happy you got an upvote for this, don't ask for more. – Shomz Nov 17 '14 at 12:53

score 0 · Answer 3 · answered Nov 17 '14 at 12:39

Reason is you are using it as select instead of when doing subtraction. Use it as below:

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` ,          orders_history.`quantity` , orders_history.`quantity` - IFNULL( esquantity, 0 ) AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

score 0 · Answer 4 · answered Nov 17 '14 at 12:42

select temp.id, items, transaction_id, quantity, ifNULL(esquantity, 0), ifNULL(myquantity, 0)
from (SELECT
  orders_history.id,
  orders_history.`items`,
  orders_history.`transaction_id`,
  orders_history.`quantity`,
  estockClaims.`esquantity`
  orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims
  ON orders_history.transaction_id = estockClaims.transaction_id
 AND orders_history.items = estockClaims.items
LIMIT 0 , 100) temp

Didn't downvote, but you should always add an explanation, code-only answers are very likely to get downvoted. — Daniel W., Nov 17 '14 at 13:04

score 0 · Answer 5 · answered Nov 17 '14 at 12:45

You can also use COALESCE to replace the NULL value to 0

check this query.

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` , 
       orders_history.`quantity` , COALESCE( esquantity, 0 ) AS esquantity, 
       orders_history.`quantity` - COALESCE(estockClaims.`esquantity`, 0) AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

Change Null with 0

5 Answers5