Search code examples
sqloraclenullsqldatatypes

Oracle SQL Check Emptiness of a NUMBER data type field

I have NUMBER(10) column "list_id" and wanted to check if the field is either empty or null.

So I run the following sql statement :

select list_id from item_list where list_id is null

This gave me about 200 rows where the field is totally empty (no data)

And then I run the following sql statement: 

select count (list_id) from item_list where list_id is null

To my surprise this returned count(list_id) = 0

Is there something I am missing ? What I am trying to achieve is find those rows where the list_id is empty or null or contains no data.

Solution

  • Use count(*):

    select count(*)
from item_list
where list_id is null;

    By definition, count(<expression>) counts the number of rows with non-NULL values for the expression. This is well documented, as well as being how COUNT() works in all databases:

    If you specify expr, then COUNT returns the number of rows where expr is not null.