Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!
Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.
Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!
SQLite - Avoiding NULL returns (COALESCO)
January 30, 2020 12:22 AM
Sometimes a SELECT returns NULL values, for example when no records were found, for example in something like this:
SELECT (SELECT someid FROM sometable WHERE somevalue=something) ...
The sub query COULD return a NULL value, and when trying to read this in an application that tries to convert this NULL to a number (integer for example) you will potentially run into an error - "" not an Integer;
To Avoid NULL values, initially I thought to define the fields as NOT NULL with a DEFAULT value. But that didn't work as I'd hoped.
After some searching, I found the function COALESCE which takes a variable number of arguments and returns the first value in the list that is not NULL (or NULL when all values are NULL).
So COALESCE(1,2,NULL) will return "1", and COALESCE(NULL,"a","b") will return "a".
So looking at out example query, we can make sure at least a number will be returned, say "0", by doing this:
SELECT COALESCE( (SELECT someid FROM sometable WHERE somevalue=something), 0) ...
So if the subquery returns NULL, COALESCE will return '0'.