MySQL: SELECT 0 instead of NULL
Question by Guest | Last update on 2021-05-12 | Created on 2014-03-31
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"?
Related Topics
Android Getting Sound Levels
Open Question | 1 Answer
MySQL: 3 Ways of showing Table Definition and Structure
Info | 0 Comments
MySQL: CSV Export as File stored on the Server
Tutorial | 0 Comments
MySQL: Check for NULL
Question | 1 Answer
MySQL: Regular Expressions in MySQL queries
Info | 0 Comments
MySQL: Change minimum word length for full text search
Tip | 1 Comment
MySQL: How to use IFNULL - Incorrect Parameter Count Error Message
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 article on askingbox.com. That’s how it’s done.
You can just use the function COALESCE().
Here is a small example:
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.
Last update on 2021-05-12 | Created on 2014-03-31
Instead of COALESCE(), you can also use the function IFNULL().
The construct can be used similar to COALESCE():
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().
2014-04-01 at 15:31
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.
Last update on 2021-05-12 | Created on 2014-04-01
Another possibility is to use the IF condition of MySQL:
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".
2015-07-08 at 15:52