Return 0 if field is null in MySQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Return 0 if field is null in MySQL

Examples related to sql

Examples related to mysql