Return 0 if field is null in MySQL

184

votes

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?

sqlmysql

371

votes

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.

26

votes

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

17

votes

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.

5

votes

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.

5

votes

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;

Return 0 if field is null in MySQL

5 Answers