Return 0 if field is null in MySQL

Question

I am trying to pull data from multiple tables to do accounting computation determining credit balance on customer accounts.

If any of the total fields are NULL, I want MySQL to return 0 for those fields.

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.

How can I return 0 for NULL in MySQL?

Answer 1

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.

Answer 2

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).