Page 1 of 1

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 N...
Clear all

SQLite - Avoiding NULL returns (COALESCO)

1 Posts
1 Users
Famed Member Admin
Joined: 10 years ago
Posts: 2504
Topic starter  

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


Like what you see and you'd like to help out? 

The best way to help is of course by assisting others with their questions here in the forum, but you can also help us out in other ways:

- Do your shopping at Amazon, it will not cost you anything extra but may generate a small commission for us,
- send a cup of coffee through PayPal ($5, $10, $20, or custom amount),
- become a Patreon,
- donate BitCoin (BTC), or BitCoinCash (BCH).