Mysql: Typecasting Null to 0

MySQL: Typecasting NULL to 0

Use IFNULL(column, 0) to convert the column value to zero.

Alternatively, the COALESCE function will do the same thing: COALESCE(column, 0), except

1. COALESCE is ANSI-compliant, IFNULL is not
2. COALESCE takes an arbitrary number of columns/values and will return the first non-null value passed to it.

Replace null with 0 in MySQL

Yes, by using COALESCE.

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

COALESCE goes through the list of values you give it, and returns the first non-null value.

MySQL Cast NULL to integer 0

You'd probably want to use the COALESCE() function:

SELECT COALESCE(col, 0) FROM `table`;

COALESCE() returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Test case:

CREATE TABLE `table` (id int, col int);

INSERT INTO `table` VALUES (1, 100);
INSERT INTO `table` VALUES (2, NULL);
INSERT INTO `table` VALUES (3, 300);
INSERT INTO `table` VALUES (4, NULL);

Result:

+------------------+
| COALESCE(col, 0) |
+------------------+
|              100 |
|                0 |
|              300 |
|                0 |
+------------------+
4 rows in set (0.00 sec)

SQL query, treat NULL as zero

You can use COALESCE. It accepts a number of arguments and returns the first one that is not null.

You can use IFNULL too (not to be confused with ISNULL). It behaves the same in this scenario, but COALESCE is more portable; it allows multiple arguments and returns the first not-null one. Also, other databases support it too, so that makes it slightly easier to migrate to another database if you would like to in the future.

SELECT COALESCE(SALARY, 0) + COALESCE(BONUS, 0)
FROM EMPLOYEE_PAY_TBL;

or

SELECT IFNULL(SALARY, 0) + IFNULL(BONUS, 0)
FROM EMPLOYEE_PAY_TBL;

Both of them are just a very convenient way to write:

SELECT 
  CASE WHEN SALARY IS NULL THEN 0 ELSE SALARY END +
  CASE WHEN BONUS IS NULL THEN 0 ELSE BONUS END
FROM EMPLOYEE_PAY_TBL;

Adding columns in MySQL - how to treat NULL as 0

use the IFNULL function

SELECT IFNULL(var1, 0) + IFNULL(var2, 0) FROM test

Return a value of 0 or 0.00 along with where clause MySQL

One way would be to wrap your subquery with COALESCE() and use zero as a default value:

SELECT
    a.ID AS EMPLOYEE,
    a.DOB AS Date,
    a.AMOUNT AS DECLARED, 
    COALESCE(
        (SELECT
            CASE WHEN SUM(gndsale.AMOUNT) AND gndsale.ID IS NULL AND gndsale.typeid = "10"
                 THEN 0 ELSE SUM(gndsale.AMOUNT) END
        FROM gndsale
        WHERE gndsale.ID = b.ID AND
              gndsale.typeid = "10" AND
              gndsale.DOB = a.DOB), 0) AS `SENIOR DISCOUNT` 
FROM gndsale a
INNER JOIN emp b
    ON a.ID = b.ID
WHERE a.type = "22" AND
      STR_TO_DATE(a.DOB, '%m/%d/%Y') BETWEEN '2017-05-01' AND '2017-05-31' 
GROUP BY DECLARED
ORDER BY STR_TO_DATE(a.DOB, '%m/%d/%Y')

---

Related Topics