Replace null with 0 in MySQL

Question

I am getting NULL values in the results of an operation in MySQL.

Is there a way to convert the NULL values into the value 0?

Teekin · Accepted Answer · 2017-05-09T10:06:12.307

128

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.

edited May 09 '17 at 10:06

answered Aug 20 '10 at 16:19

Teekin

3

+1: `COALESCE` is ANSI, supported by SQL Server 2000+, Oracle 9i+, MySQL 4.1+, dunno the version of PostgreSQL or SQLite... – OMG Ponies Aug 20 '10 at 16:27
1

MySQL is mentioned in the title. I added a tag for it. – D'Arcy Rittich Aug 20 '10 at 16:33
2

I think every 3VL DB with a SQL interface supports `coalesce()`. Case and point, [it was in MySQL 3.23](http://www.norrnod.se/norrnod/dokumentation/mysql/3.23.45/manual_toc.html#Comparison_Operators) which is about as close to the bottom of the barrel as you can get. – Evan Carroll Aug 20 '10 at 16:34

score 94 · Answer 2 · answered Jun 15 '14 at 05:55

94

I am adding this answer because no one mentioned IFNULL function

You can use IFNULL

SELECT IFNULL(column_name, 0) FROM table_name;

IFNULL will return column's value (if it has something other than NULL) otherwise second parameter passed (in this case 0).

answered Jun 15 '14 at 05:55

Arif

Thanks, run perfect in mysql. – iarroyo Aug 05 '15 at 09:03
This only really selects and replaces, rather than truly updating the table – tedioustortoise May 18 '20 at 15:19
can i do it with multiple columns? – Xin Niu Sep 19 '20 at 05:29
@XinNiu yes you can. `SELECT IFNULL(column1, column2) FROM table`. or `SELECT IFNULL(column1, 0) AS alias1, IFNULL(column2, 0) AS alias2 FROM table`. BUT if you want to use more than 2 columns in one expression then you will need to use `COALESCE` like this `SELECT COALESCE(column1, column2, column3, column4, 0) FROM table`. `COALESCE ` will return first non-null value in the parameters. – Arif Sep 28 '20 at 08:52

Alex Khimich · Answer 3 · 2018-12-17T21:29:37.773

26

If you messed up and have NULLs in existing table layout and want zeros, here is solution:

UPDATE `table` SET `somefield`=0 WHERE `somefield` is null

edited Dec 17 '18 at 21:29

answered Dec 17 '18 at 21:15

Alex Khimich

2

this is the best answer – Barney Chambers May 10 '19 at 09:28
This is exatcly i wanted.. Thank you – majurageerthan Oct 19 '19 at 06:15
I almost got tricked by the accepted answer. This is exactly what I wanted. Thank you! – Tom Feb 17 '21 at 07:48

score 8 · Answer 4 · answered Aug 20 '10 at 16:19

8

There is the COALESCE method which return the first non-null parameter, in your case :

COALESCE(field, 0)

But you can use this if you want more :

COALESCE(field1, field2, 0)

answered Aug 20 '10 at 16:19

Colin Hebert

score 4 · Answer 5 · answered Aug 20 '10 at 16:20

4

MySQL:

SELECT COALESCE(Mycolumn, 0);

answered Aug 20 '10 at 16:20

Sjoerd

score 0 · Answer 6 · answered Jan 19 '20 at 22:05

0

you can put the 0 value into your insert input method by casting it:(int)0

answered Jan 19 '20 at 22:05

Alireza Saffar

6 Answers6