MySQL Cast NULL to integer 0

Question

How can I cast something that returns NULL to 0?

If this is my query: select col from table; would this be the right way to do it: select cast(col as unsigned integer) from table;?

Thank you.

Solution 1

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)

Solution 2

You can also use the IFNULL() function:

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

IFNULL(expr1, expr2) returns the first expression if it's not null, else returns the second expression.

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:

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