Thursday, July 28, 2011

Counting NULL Values

You should not use = NULL to check if something is NULL. You should use IS NULL instead. First find a table or view which has a column containing some null values and use the NVL function to count them:

SQL> select count(*) from dba_tab_comments
  2  where nvl(comments,'NULL') = 'NULL'
  3  /

  COUNT(*)
----------
      2354

SQL>


Count them again using IS NULL. The answer will be the same:

SQL> select count(*) from dba_tab_comments
  2  where comments is null
  3  /

  COUNT(*)
----------
      2354

SQL>


Finally, count them using = NULL. This will not find the null values:


SQL> select count(*) from dba_tab_comments
  2  where comments = null
  3  /

  COUNT(*)
----------
         0

SQL>

No comments:

Post a Comment