MySQL: Select a default value for NULL fields

If you need to do calculations inside the database and can not use Ruby objects you may run into problems when encountering fields with NULL values:

SELECT foo, bar, foo - bar AS baz FROM plop;
+-----+------+------+
| foo | bar  | baz  |
+-----+------+------+
|  30 |   20 |   10 |
|  30 | NULL | NULL |
+-----+------+------+

Solve this by using IFNULL: it returns the selected value if present and a given alternative if it would select NULL:

SELECT foo, bar, foo - IFNULL(bar, 0) AS baz FROM plop;
+-----+------+-----+
| foo | bar  | baz |
+-----+------+-----+
|  30 |   20 |  10 |
|  30 | NULL |  30 |
+-----+------+-----+

Arne Hartherz

makandra.com

Say thanks12

Last edit

2015-02-16

Arne Hartherz

License

Source code in this card is licensed under the MIT License.

Posted by Arne Hartherz to makandra dev (2010-11-30 09:49)