00 Votes

SQLite: Read out 0 if field is NULL

Question by SmartUser | 2016-09-19 at 23:33

Is there any possibility or function available in SQLite which can make the query always return the number 0 in case of the value would actually be NULL?

I often have the problem that some fields are not assigned or defined (for example in some JOIN queries) and in this case, I would just like to have a 0 as result, because NULL is sometimes making programs when trying to pass it to someone further functions. Is there any possibility?

2Best Answer2 Votes

You are searching for the function IFNULL(). You can pass two values to this function and you always get the first of those values that is not NULL.

As an example, here, we would like to read out the value of "points" from the table "users":

SELECT points FROM users WHERE id = 1;

If "points" might be NULL in some cases and if we want to have 0 in our resulting data records, we can make use of IFNULL() in the following way:

SELECT IFNULL(points, 0) FROM users WHERE id = 1;

The first argument of IFNULL() is the value coming from the column "points". If this value is not NULL, the value will be returned. However, if this values is NULL, the second argument will be used as return value.

Of course, instead of specifying 0 as the second argument, we can also use any other value or even another field that should be read out instead. By the way, if both passed arguments are NULL, also IFNULL() is returning the value NULL.

If we want to specify more than two arguments from which the first non null value should be selected, we can use the function COALESCE() instead. This function is doing the same as IFNULL(), but it can accept an arbitrary number of arguments.
2016-09-20 at 16:08

ReplyPositive Negative

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.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.