CODEWITHSUNDEEP
mysqlsqlnull
NullVoxPopuli
NullVoxPopuli

Reputation: 65103

Replace null with 0 in MySQL

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?

Upvotes: 95

Views: 163287

Answers (6)

Teekin
Teekin

Reputation: 13259

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.

Upvotes: 140

Alireza Saffar
Alireza Saffar

Reputation: 1

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

Upvotes: 0

Alex Khimich
Alex Khimich

Reputation: 828

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

Upvotes: 26

Arif
Arif

Reputation: 1726

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).

Upvotes: 104

Sjoerd
Sjoerd

Reputation: 75588

MySQL:

SELECT COALESCE(Mycolumn, 0);

Upvotes: 4

Colin Hebert
Colin Hebert

Reputation: 93157

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)

Upvotes: 9

Related Questions