Reputation: 10112
I use SQLite with R to store data that are too large for RAM. This allows me to SELECT and JOIN outside of RAM. My data are in general well-groomed, but sometimes there are null entries and I have no prior on where these null entries might be (nor do I care, I am happy to omit those rows).
Is there a way that I can not SELECT or return rows with null entries without specifically testing each column? Or replace all null entries with 0 or NA? Thanks!
Upvotes: 0
Views: 2453
Reputation: 3973
Try the COALESCE
function:
SELECT COALESCE(myColumn, 'valueIfNull')
FROM Table
Upvotes: 2
Reputation: 839264
Is there a way that I can not SELECT or return rows with null entries without specifically testing each column?
To do this you need to test every column to see if it is NULL.
However if you don't like writing WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND ...
then you could try using coalesce
or if all your columns have the same type you could try max
:
SELECT col1, col2, col3
FROM yourtable
WHERE max(col1, col2, col3) IS NOT NULL
Upvotes: 3