Return 0 if field is null in MySQL

In MySQL, is there a way to set the "total" fields to zero if they are NULL?

Here is what I have:

SELECT uo.order_id, uo.order_total, uo.order_status, (SELECT SUM(uop.price * uop.qty) FROM uc_order_products uop WHERE uo.order_id = uop.order_id ) AS products_subtotal, (SELECT SUM(upr.amount) FROM uc_payment_receipts upr WHERE uo.order_id = upr.order_id ) AS payment_received, (SELECT SUM(uoli.amount) FROM uc_order_line_items uoli WHERE uo.order_id = uoli.order_id ) AS line_item_subtotal FROM uc_orders uo WHERE uo.order_status NOT IN ("future", "canceled") AND uo.uid = 4172; 

The data comes out fine, except the NULL fields should be 0.

How can I return 0 for NULL in MySQL?

5 Answers

Use IFNULL:

IFNULL(expr1, 0) 

From the documentation:

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

You can use coalesce(column_name,0) instead of just column_name. The coalesce function returns the first non-NULL value in the list.

I should mention that per-row functions like this are usually problematic for scalability. If you think your database may get to be a decent size, it's often better to use extra columns and triggers to move the cost from the select to the insert/update.

This amortises the cost assuming your database is read more often than written (and most of them are).

None of the above answers were complete for me. If your field is named field, so the selector should be the following one:

IFNULL(`field`,0) AS field 

For example in a SELECT query:

SELECT IFNULL(`field`,0) AS field, `otherfield` FROM `mytable` 

Hope this can help someone to not waste time.

You can try something like this

IFNULL(NULLIF(X, '' ), 0) 

Attribute X is assumed to be empty if it is an empty String, so after that you can declare as a zero instead of last value. In another case, it would remain its original value.

Anyway, just to give another way to do that.

Yes IFNULL function will be working to achieve your desired result.

SELECT uo.order_id, uo.order_total, uo.order_status, (SELECT IFNULL(SUM(uop.price * uop.qty),0) FROM uc_order_products uop WHERE uo.order_id = uop.order_id ) AS products_subtotal, (SELECT IFNULL(SUM(upr.amount),0) FROM uc_payment_receipts upr WHERE uo.order_id = upr.order_id ) AS payment_received, (SELECT IFNULL(SUM(uoli.amount),0) FROM uc_order_line_items uoli WHERE uo.order_id = uoli.order_id ) AS line_item_subtotal FROM uc_orders uo WHERE uo.order_status NOT IN ("future", "canceled") AND uo.uid = 4172;