1 3 Votes

MySQL: SELECT 0 instead of NULL

Question by Guest | 31/03/2014 at 17:34

In one of my MySQL queries, it can happen from time to time that in the results there is the one or the other NULL value, because the corresponding cell has not yet been added to the database.

To nevertheless be able to process the values in my PHP script, I would like to make MySQL return the number "0" instead of "NULL".

Is there any possibility in MySQL to determine directly in the query that "0" should be returned whenever the result is "NULL"?

ReplyPositiveNegativeDateVotes

Stefan Trost

Show Profile | Message
Avatar
3Best Answer
3 Votes

You can just use the function COALESCE().

Here is an small example:

SELECT id, COALESCE(col, 0) FROM tab

In this query, we would like to read the columns "id" and "col" from the table "tab". In the case that the value coming from "col" is "NULL", we would like to have "0" rather than "NULL".

COALESCE() returns the first value that is not NULL from the row of passed parameters. Thus, using COALESCE(col, 1) would return "1" whenever "col" is NULL.

You can also use this function with multiple parameters. For example, COALESCE(col1, col2, 0). Here, the result would be "col1" whenever the value from "col1" is not NULL. If "col1" is NULL, MySQL tries the value from "col2" and returns it if it is not NULL. If both, "col1" and "col2" are NULL, MySQL falls back on 0.
31/03/2014 at 20:00

ReplyPositive Negative

Computer Expert

Show Profile | Message
Avatar
-33 Votes

Instead of COALESCE(), you can also use the function IFNULL().

The construct can be used similar to COALESCE():

SELECT id, IFNULL(col, 0) FROM tab

That is: If the first value passed to IFNULL is NULL (here the value from "col"), take the second value (here 0).

However, you can only use IFNULL() with 2 parameters. So, it is not as flexible in use as COALESCE().
01/04/2014 at 15:31

ReplyPositive Negative

Anja Proggy

Show Profile | Message
Avatar
-13 Votes

Another little tip: In MySQL, a DEFAULT value can be defined for each column.

Perhaps this can help you. Because, if you set the DEFAULT value of your column to "0", this value is written automatically to each new cell when a new line is added to the database.

Of course, this tip cannot be used when trying values from rows that are non-existent.
01/04/2014 at 15:32

ReplyPositive Negative

Stefan Trost

Show Profile | Message
Avatar
46 Votes

Another possibility is to use the IF condition of MySQL:

SELECT id, IF(col IS NULL, 0, col) FROM tab

In this IF condition, we check whether "col" is NULL via "col IS NULL". If yes, we return the alternative (here that is 0); if no, we return the content of "col".
08/07/2015 at 15:52

ReplyPositive Negative
Reply

Related Topics

MySQL: Check for NULL
Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.